USE [SkillifyTMP]
GO
/****** Object:  StoredProcedure [dbo].[aspnet_AnyDataInTables]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_AnyDataInTables]
    @TablesToCheck int
AS
BEGIN
    -- Check Membership table if (@TablesToCheck & 1) is set
    IF ((@TablesToCheck & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Membership))
        BEGIN
            SELECT N'aspnet_Membership'
            RETURN
        END
    END

    -- Check aspnet_Roles table if (@TablesToCheck & 2) is set
    IF ((@TablesToCheck & 2) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Roles') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 RoleId FROM dbo.aspnet_Roles))
        BEGIN
            SELECT N'aspnet_Roles'
            RETURN
        END
    END

    -- Check aspnet_Profile table if (@TablesToCheck & 4) is set
    IF ((@TablesToCheck & 4) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Profile))
        BEGIN
            SELECT N'aspnet_Profile'
            RETURN
        END
    END

    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 8) is set
    IF ((@TablesToCheck & 8) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_PersonalizationPerUser))
        BEGIN
            SELECT N'aspnet_PersonalizationPerUser'
            RETURN
        END
    END

    -- Check aspnet_PersonalizationPerUser table if (@TablesToCheck & 16) is set
    IF ((@TablesToCheck & 16) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_WebEvent_LogEvent') AND (type = 'P'))) )
    BEGIN
        IF (EXISTS(SELECT TOP 1 * FROM dbo.aspnet_WebEvent_Events))
        BEGIN
            SELECT N'aspnet_WebEvent_Events'
            RETURN
        END
    END

    -- Check aspnet_Users table if (@TablesToCheck & 1,2,4 & 8) are all set
    IF ((@TablesToCheck & 1) <> 0 AND
        (@TablesToCheck & 2) <> 0 AND
        (@TablesToCheck & 4) <> 0 AND
        (@TablesToCheck & 8) <> 0 AND
        (@TablesToCheck & 32) <> 0 AND
        (@TablesToCheck & 128) <> 0 AND
        (@TablesToCheck & 256) <> 0 AND
        (@TablesToCheck & 512) <> 0 AND
        (@TablesToCheck & 1024) <> 0)
    BEGIN
        IF (EXISTS(SELECT TOP 1 UserId FROM dbo.aspnet_Users))
        BEGIN
            SELECT N'aspnet_Users'
            RETURN
        END
        IF (EXISTS(SELECT TOP 1 ApplicationId FROM dbo.aspnet_Applications))
        BEGIN
            SELECT N'aspnet_Applications'
            RETURN
        END
    END
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Applications_CreateApplication]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Applications_CreateApplication]
    @ApplicationName      nvarchar(256),
    @ApplicationId        uniqueidentifier OUTPUT
AS
BEGIN
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

    IF(@ApplicationId IS NULL)
    BEGIN
        DECLARE @TranStarted   bit
        SET @TranStarted = 0

        IF( @@TRANCOUNT = 0 )
        BEGIN
	        BEGIN TRANSACTION
	        SET @TranStarted = 1
        END
        ELSE
    	    SET @TranStarted = 0

        SELECT  @ApplicationId = ApplicationId
        FROM dbo.aspnet_Applications WITH (UPDLOCK, HOLDLOCK)
        WHERE LOWER(@ApplicationName) = LoweredApplicationName

        IF(@ApplicationId IS NULL)
        BEGIN
            SELECT  @ApplicationId = NEWID()
            INSERT  dbo.aspnet_Applications (ApplicationId, ApplicationName, LoweredApplicationName)
            VALUES  (@ApplicationId, @ApplicationName, LOWER(@ApplicationName))
        END


        IF( @TranStarted = 1 )
        BEGIN
            IF(@@ERROR = 0)
            BEGIN
	        SET @TranStarted = 0
	        COMMIT TRANSACTION
            END
            ELSE
            BEGIN
                SET @TranStarted = 0
                ROLLBACK TRANSACTION
            END
        END
    END
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_CheckSchemaVersion]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_CheckSchemaVersion]
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
    IF (EXISTS( SELECT  *
                FROM    dbo.aspnet_SchemaVersions
                WHERE   Feature = LOWER( @Feature ) AND
                        CompatibleSchemaVersion = @CompatibleSchemaVersion ))
        RETURN 0

    RETURN 1
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_ChangePasswordQuestionAndAnswer]
    @ApplicationName       nvarchar(256),
    @UserName              nvarchar(256),
    @NewPasswordQuestion   nvarchar(256),
    @NewPasswordAnswer     nvarchar(128)
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Membership m, dbo.aspnet_Users u, dbo.aspnet_Applications a
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId
    IF (@UserId IS NULL)
    BEGIN
        RETURN(1)
    END

    UPDATE dbo.aspnet_Membership
    SET    PasswordQuestion = @NewPasswordQuestion, PasswordAnswer = @NewPasswordAnswer
    WHERE  UserId=@UserId
    RETURN(0)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_CreateUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_CreateUser]
    @ApplicationName                        nvarchar(256),
    @UserName                               nvarchar(256),
    @Password                               nvarchar(128),
    @PasswordSalt                           nvarchar(128),
    @Email                                  nvarchar(256),
    @PasswordQuestion                       nvarchar(256),
    @PasswordAnswer                         nvarchar(128),
    @IsApproved                             bit,
    @CurrentTimeUtc                         datetime,
    @CreateDate                             datetime = NULL,
    @UniqueEmail                            int      = 0,
    @PasswordFormat                         int      = 0,
    @UserId                                 uniqueidentifier OUTPUT
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @NewUserId uniqueidentifier
    SELECT @NewUserId = NULL

    DECLARE @IsLockedOut bit
    SET @IsLockedOut = 0

    DECLARE @LastLockoutDate  datetime
    SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAttemptCount int
    SET @FailedPasswordAttemptCount = 0

    DECLARE @FailedPasswordAttemptWindowStart  datetime
    SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @FailedPasswordAnswerAttemptCount int
    SET @FailedPasswordAnswerAttemptCount = 0

    DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime
    SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

    DECLARE @NewUserCreated bit
    DECLARE @ReturnValue   int
    SET @ReturnValue = 0

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    SET @CreateDate = @CurrentTimeUtc

    SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
    IF ( @NewUserId IS NULL )
    BEGIN
        SET @NewUserId = @UserId
        EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
        SET @NewUserCreated = 1
    END
    ELSE
    BEGIN
        SET @NewUserCreated = 0
        IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
        BEGIN
            SET @ErrorCode = 6
            GOTO Cleanup
        END
    END

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @ReturnValue = -1 )
    BEGIN
        SET @ErrorCode = 10
        GOTO Cleanup
    END

    IF ( EXISTS ( SELECT UserId
                  FROM   dbo.aspnet_Membership
                  WHERE  @NewUserId = UserId ) )
    BEGIN
        SET @ErrorCode = 6
        GOTO Cleanup
    END

    SET @UserId = @NewUserId

    IF (@UniqueEmail = 1)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
                    WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
        BEGIN
            SET @ErrorCode = 7
            GOTO Cleanup
        END
    END

    IF (@NewUserCreated = 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate = @CreateDate
        WHERE  @UserId = UserId
        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    INSERT INTO dbo.aspnet_Membership
                ( ApplicationId,
                  UserId,
                  Password,
                  PasswordSalt,
                  Email,
                  LoweredEmail,
                  PasswordQuestion,
                  PasswordAnswer,
                  PasswordFormat,
                  IsApproved,
                  IsLockedOut,
                  CreateDate,
                  LastLoginDate,
                  LastPasswordChangedDate,
                  LastLockoutDate,
                  FailedPasswordAttemptCount,
                  FailedPasswordAttemptWindowStart,
                  FailedPasswordAnswerAttemptCount,
                  FailedPasswordAnswerAttemptWindowStart )
         VALUES ( @ApplicationId,
                  @UserId,
                  @Password,
                  @PasswordSalt,
                  @Email,
                  LOWER(@Email),
                  @PasswordQuestion,
                  @PasswordAnswer,
                  @PasswordFormat,
                  @IsApproved,
                  @IsLockedOut,
                  @CreateDate,
                  @CreateDate,
                  @CreateDate,
                  @LastLockoutDate,
                  @FailedPasswordAttemptCount,
                  @FailedPasswordAttemptWindowStart,
                  @FailedPasswordAnswerAttemptCount,
                  @FailedPasswordAnswerAttemptWindowStart )

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_FindUsersByEmail]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_FindUsersByEmail]
    @ApplicationName       nvarchar(256),
    @EmailToMatch          nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    IF( @EmailToMatch IS NULL )
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT u.UserId
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.Email IS NULL
            ORDER BY m.LoweredEmail
    ELSE
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT u.UserId
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
            WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND m.LoweredEmail LIKE LOWER(@EmailToMatch)
            ORDER BY m.LoweredEmail

    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY m.LoweredEmail

    SELECT  @TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    RETURN @TotalRecords
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_FindUsersByName]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_FindUsersByName]
    @ApplicationName       nvarchar(256),
    @UserNameToMatch       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT u.UserId
        FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch)
        ORDER BY u.UserName


    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName

    SELECT  @TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    RETURN @TotalRecords
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_GetAllUsers]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetAllUsers]
    @ApplicationName       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0


    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
    SELECT u.UserId
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName

    SELECT @TotalRecords = @@ROWCOUNT

    SELECT u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName
    RETURN @TotalRecords
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_GetNumberOfUsersOnline]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetNumberOfUsersOnline]
    @ApplicationName            nvarchar(256),
    @MinutesSinceLastInActive   int,
    @CurrentTimeUtc             datetime
AS
BEGIN
    DECLARE @DateActive datetime
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

    DECLARE @NumOnline int
    SELECT  @NumOnline = COUNT(*)
    FROM    dbo.aspnet_Users u(NOLOCK),
            dbo.aspnet_Applications a(NOLOCK),
            dbo.aspnet_Membership m(NOLOCK)
    WHERE   u.ApplicationId = a.ApplicationId                  AND
            LastActivityDate > @DateActive                     AND
            a.LoweredApplicationName = LOWER(@ApplicationName) AND
            u.UserId = m.UserId
    RETURN(@NumOnline)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_GetPassword]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetPassword]
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @MaxInvalidPasswordAttempts     int,
    @PasswordAttemptWindow          int,
    @CurrentTimeUtc                 datetime,
    @PasswordAnswer                 nvarchar(128) = NULL
AS
BEGIN
    DECLARE @UserId                                 uniqueidentifier
    DECLARE @PasswordFormat                         int
    DECLARE @Password                               nvarchar(128)
    DECLARE @passAns                                nvarchar(128)
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId,
            @Password = m.Password,
            @passAns = m.PasswordAnswer,
            @PasswordFormat = m.PasswordFormat,
            @IsLockedOut = m.IsLockedOut,
            @LastLockoutDate = m.LastLockoutDate,
            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF ( @@rowcount = 0 )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    IF( @IsLockedOut = 1 )
    BEGIN
        SET @ErrorCode = 99
        GOTO Cleanup
    END

    IF ( NOT( @PasswordAnswer IS NULL ) )
    BEGIN
        IF( ( @passAns IS NULL ) OR ( LOWER( @passAns ) <> LOWER( @PasswordAnswer ) ) )
        BEGIN
            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
            END

            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @CurrentTimeUtc
                END
            END

            SET @ErrorCode = 3
        END
        ELSE
        BEGIN
            IF( @FailedPasswordAnswerAttemptCount > 0 )
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = 0
                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            END
        END

        UPDATE dbo.aspnet_Membership
        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
        WHERE @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    IF( @ErrorCode = 0 )
        SELECT @Password, @PasswordFormat

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_GetPasswordWithFormat]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetPasswordWithFormat]
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @UpdateLastLoginActivityDate    bit,
    @CurrentTimeUtc                 datetime
AS
BEGIN
    DECLARE @IsLockedOut                        bit
    DECLARE @UserId                             uniqueidentifier
    DECLARE @Password                           nvarchar(128)
    DECLARE @PasswordSalt                       nvarchar(128)
    DECLARE @PasswordFormat                     int
    DECLARE @FailedPasswordAttemptCount         int
    DECLARE @FailedPasswordAnswerAttemptCount   int
    DECLARE @IsApproved                         bit
    DECLARE @LastActivityDate                   datetime
    DECLARE @LastLoginDate                      datetime

    SELECT  @UserId          = NULL

    SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
            @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
            @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF (@UserId IS NULL)
        RETURN 1

    IF (@IsLockedOut = 1)
        RETURN 99

    SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
             @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate

    IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
    BEGIN
        UPDATE  dbo.aspnet_Membership
        SET     LastLoginDate = @CurrentTimeUtc
        WHERE   UserId = @UserId

        UPDATE  dbo.aspnet_Users
        SET     LastActivityDate = @CurrentTimeUtc
        WHERE   @UserId = UserId
    END


    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_GetUserByEmail]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
    @ApplicationName  nvarchar(256),
    @Email            nvarchar(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                u.UserId = m.UserId AND
                LOWER(@Email) = m.LoweredEmail

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_GetUserByName]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier

    IF (@UpdateLastActivity = 1)
    BEGIN
        -- select user ID from aspnet_users table
        SELECT TOP 1 @UserId = u.UserId
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1

        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId

        SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut, m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
    END
    ELSE
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE    LOWER(@ApplicationName) = a.LoweredApplicationName AND
                u.ApplicationId = a.ApplicationId    AND
                LOWER(@UserName) = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1
    END

    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_GetUserByUserId]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_GetUserByUserId]
    @UserId               uniqueidentifier,
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    IF ( @UpdateLastActivity = 1 )
    BEGIN
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        FROM     dbo.aspnet_Users
        WHERE    @UserId = UserId

        IF ( @@ROWCOUNT = 0 ) -- User ID not found
            RETURN -1
    END

    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
            m.LastLockoutDate
    FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   @UserId = u.UserId AND u.UserId = m.UserId

    IF ( @@ROWCOUNT = 0 ) -- User ID not found
       RETURN -1

    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_ResetPassword]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_ResetPassword]
    @ApplicationName             nvarchar(256),
    @UserName                    nvarchar(256),
    @NewPassword                 nvarchar(128),
    @MaxInvalidPasswordAttempts  int,
    @PasswordAttemptWindow       int,
    @PasswordSalt                nvarchar(128),
    @CurrentTimeUtc              datetime,
    @PasswordFormat              int = 0,
    @PasswordAnswer              nvarchar(128) = NULL
AS
BEGIN
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @UserId                                 uniqueidentifier
    SET     @UserId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF ( @UserId IS NULL )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    SELECT @IsLockedOut = IsLockedOut,
           @LastLockoutDate = LastLockoutDate,
           @FailedPasswordAttemptCount = FailedPasswordAttemptCount,
           @FailedPasswordAttemptWindowStart = FailedPasswordAttemptWindowStart,
           @FailedPasswordAnswerAttemptCount = FailedPasswordAnswerAttemptCount,
           @FailedPasswordAnswerAttemptWindowStart = FailedPasswordAnswerAttemptWindowStart
    FROM dbo.aspnet_Membership WITH ( UPDLOCK )
    WHERE @UserId = UserId

    IF( @IsLockedOut = 1 )
    BEGIN
        SET @ErrorCode = 99
        GOTO Cleanup
    END

    UPDATE dbo.aspnet_Membership
    SET    Password = @NewPassword,
           LastPasswordChangedDate = @CurrentTimeUtc,
           PasswordFormat = @PasswordFormat,
           PasswordSalt = @PasswordSalt
    WHERE  @UserId = UserId AND
           ( ( @PasswordAnswer IS NULL ) OR ( LOWER( PasswordAnswer ) = LOWER( @PasswordAnswer ) ) )

    IF ( @@ROWCOUNT = 0 )
        BEGIN
            IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAnswerAttemptWindowStart ) )
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = 1
            END
            ELSE
            BEGIN
                SET @FailedPasswordAnswerAttemptWindowStart = @CurrentTimeUtc
                SET @FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount + 1
            END

            BEGIN
                IF( @FailedPasswordAnswerAttemptCount >= @MaxInvalidPasswordAttempts )
                BEGIN
                    SET @IsLockedOut = 1
                    SET @LastLockoutDate = @CurrentTimeUtc
                END
            END

            SET @ErrorCode = 3
        END
    ELSE
        BEGIN
            IF( @FailedPasswordAnswerAttemptCount > 0 )
            BEGIN
                SET @FailedPasswordAnswerAttemptCount = 0
                SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            END
        END

    IF( NOT ( @PasswordAnswer IS NULL ) )
    BEGIN
        UPDATE dbo.aspnet_Membership
        SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
            FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
            FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
            FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
            FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
        WHERE @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_SetPassword]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_SetPassword]
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @NewPassword      nvarchar(128),
    @PasswordSalt     nvarchar(128),
    @CurrentTimeUtc   datetime,
    @PasswordFormat   int = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF (@UserId IS NULL)
        RETURN(1)

    UPDATE dbo.aspnet_Membership
    SET Password = @NewPassword, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt,
        LastPasswordChangedDate = @CurrentTimeUtc
    WHERE @UserId = UserId
    RETURN(0)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_UnlockUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_UnlockUser]
    @ApplicationName                         nvarchar(256),
    @UserName                                nvarchar(256)
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF ( @UserId IS NULL )
        RETURN 1

    UPDATE dbo.aspnet_Membership
    SET IsLockedOut = 0,
        FailedPasswordAttemptCount = 0,
        FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
        FailedPasswordAnswerAttemptCount = 0,
        FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ),
        LastLockoutDate = CONVERT( datetime, '17540101', 112 )
    WHERE @UserId = UserId

    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_UpdateUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUser]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @Email                nvarchar(256),
    @Comment              ntext,
    @IsApproved           bit,
    @LastLoginDate        datetime,
    @LastActivityDate     datetime,
    @UniqueEmail          int,
    @CurrentTimeUtc       datetime
AS
BEGIN
    DECLARE @UserId uniqueidentifier
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @UserId = NULL
    SELECT  @UserId = u.UserId, @ApplicationId = a.ApplicationId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
    WHERE   LoweredUserName = LOWER(@UserName) AND
            u.ApplicationId = a.ApplicationId  AND
            LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.UserId = m.UserId

    IF (@UserId IS NULL)
        RETURN(1)

    IF (@UniqueEmail = 1)
    BEGIN
        IF (EXISTS (SELECT *
                    FROM  dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
                    WHERE ApplicationId = @ApplicationId  AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
        BEGIN
            RETURN(7)
        END
    END

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
	SET @TranStarted = 0

    UPDATE dbo.aspnet_Users WITH (ROWLOCK)
    SET
         LastActivityDate = @LastActivityDate
    WHERE
       @UserId = UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
    SET
         Email            = @Email,
         LoweredEmail     = LOWER(@Email),
         Comment          = @Comment,
         IsApproved       = @IsApproved,
         LastLoginDate    = @LastLoginDate
    WHERE
       @UserId = UserId

    IF( @@ERROR <> 0 )
        GOTO Cleanup

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN -1
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Membership_UpdateUserInfo]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUserInfo]
    @ApplicationName                nvarchar(256),
    @UserName                       nvarchar(256),
    @IsPasswordCorrect              bit,
    @UpdateLastLoginActivityDate    bit,
    @MaxInvalidPasswordAttempts     int,
    @PasswordAttemptWindow          int,
    @CurrentTimeUtc                 datetime,
    @LastLoginDate                  datetime,
    @LastActivityDate               datetime
AS
BEGIN
    DECLARE @UserId                                 uniqueidentifier
    DECLARE @IsApproved                             bit
    DECLARE @IsLockedOut                            bit
    DECLARE @LastLockoutDate                        datetime
    DECLARE @FailedPasswordAttemptCount             int
    DECLARE @FailedPasswordAttemptWindowStart       datetime
    DECLARE @FailedPasswordAnswerAttemptCount       int
    DECLARE @FailedPasswordAnswerAttemptWindowStart datetime

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    SELECT  @UserId = u.UserId,
            @IsApproved = m.IsApproved,
            @IsLockedOut = m.IsLockedOut,
            @LastLockoutDate = m.LastLockoutDate,
            @FailedPasswordAttemptCount = m.FailedPasswordAttemptCount,
            @FailedPasswordAttemptWindowStart = m.FailedPasswordAttemptWindowStart,
            @FailedPasswordAnswerAttemptCount = m.FailedPasswordAnswerAttemptCount,
            @FailedPasswordAnswerAttemptWindowStart = m.FailedPasswordAnswerAttemptWindowStart
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m WITH ( UPDLOCK )
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF ( @@rowcount = 0 )
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    IF( @IsLockedOut = 1 )
    BEGIN
        GOTO Cleanup
    END

    IF( @IsPasswordCorrect = 0 )
    BEGIN
        IF( @CurrentTimeUtc > DATEADD( minute, @PasswordAttemptWindow, @FailedPasswordAttemptWindowStart ) )
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
            SET @FailedPasswordAttemptCount = 1
        END
        ELSE
        BEGIN
            SET @FailedPasswordAttemptWindowStart = @CurrentTimeUtc
            SET @FailedPasswordAttemptCount = @FailedPasswordAttemptCount + 1
        END

        BEGIN
            IF( @FailedPasswordAttemptCount >= @MaxInvalidPasswordAttempts )
            BEGIN
                SET @IsLockedOut = 1
                SET @LastLockoutDate = @CurrentTimeUtc
            END
        END
    END
    ELSE
    BEGIN
        IF( @FailedPasswordAttemptCount > 0 OR @FailedPasswordAnswerAttemptCount > 0 )
        BEGIN
            SET @FailedPasswordAttemptCount = 0
            SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            SET @FailedPasswordAnswerAttemptCount = 0
            SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
            SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
        END
    END

    IF( @UpdateLastLoginActivityDate = 1 )
    BEGIN
        UPDATE  dbo.aspnet_Users
        SET     LastActivityDate = @LastActivityDate
        WHERE   @UserId = UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END

        UPDATE  dbo.aspnet_Membership
        SET     LastLoginDate = @LastLoginDate
        WHERE   UserId = @UserId

        IF( @@ERROR <> 0 )
        BEGIN
            SET @ErrorCode = -1
            GOTO Cleanup
        END
    END


    UPDATE dbo.aspnet_Membership
    SET IsLockedOut = @IsLockedOut, LastLockoutDate = @LastLockoutDate,
        FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
        FailedPasswordAttemptWindowStart = @FailedPasswordAttemptWindowStart,
        FailedPasswordAnswerAttemptCount = @FailedPasswordAnswerAttemptCount,
        FailedPasswordAnswerAttemptWindowStart = @FailedPasswordAnswerAttemptWindowStart
    WHERE @UserId = UserId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
	SET @TranStarted = 0
	COMMIT TRANSACTION
    END

    RETURN @ErrorCode

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Paths_CreatePath]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Paths_CreatePath]
    @ApplicationId UNIQUEIDENTIFIER,
    @Path           NVARCHAR(256),
    @PathId         UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
    BEGIN TRANSACTION
    IF (NOT EXISTS(SELECT * FROM dbo.aspnet_Paths WHERE LoweredPath = LOWER(@Path) AND ApplicationId = @ApplicationId))
    BEGIN
        INSERT dbo.aspnet_Paths (ApplicationId, Path, LoweredPath) VALUES (@ApplicationId, @Path, LOWER(@Path))
    END
    COMMIT TRANSACTION
    SELECT @PathId = PathId FROM dbo.aspnet_Paths WHERE LOWER(@Path) = LoweredPath AND ApplicationId = @ApplicationId
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Personalization_GetApplicationId]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Personalization_GetApplicationId] (
    @ApplicationName NVARCHAR(256),
    @ApplicationId UNIQUEIDENTIFIER OUT)
AS
BEGIN
    SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationAdministration_DeleteAllState]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_DeleteAllState] (
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @Count int OUT)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        IF (@AllUsersScope = 1)
            DELETE FROM aspnet_PersonalizationAllUsers
            WHERE PathId IN
               (SELECT Paths.PathId
                FROM dbo.aspnet_Paths Paths
                WHERE Paths.ApplicationId = @ApplicationId)
        ELSE
            DELETE FROM aspnet_PersonalizationPerUser
            WHERE PathId IN
               (SELECT Paths.PathId
                FROM dbo.aspnet_Paths Paths
                WHERE Paths.ApplicationId = @ApplicationId)

        SELECT @Count = @@ROWCOUNT
    END
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationAdministration_FindState]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_FindState] (
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @PageIndex              INT,
    @PageSize               INT,
    @Path NVARCHAR(256) = NULL,
    @UserName NVARCHAR(256) = NULL,
    @InactiveSinceDate DATETIME = NULL)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        RETURN

    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    DECLARE @TotalRecords   INT
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table to store the selected results
    CREATE TABLE #PageIndex (
        IndexId int IDENTITY (0, 1) NOT NULL,
        ItemId UNIQUEIDENTIFIER
    )

    IF (@AllUsersScope = 1)
    BEGIN
        -- Insert into our temp table
        INSERT INTO #PageIndex (ItemId)
        SELECT Paths.PathId
        FROM dbo.aspnet_Paths Paths,
             ((SELECT Paths.PathId
               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
               WHERE Paths.ApplicationId = @ApplicationId
                      AND AllUsers.PathId = Paths.PathId
                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              ) AS SharedDataPerPath
              FULL OUTER JOIN
              (SELECT DISTINCT Paths.PathId
               FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Paths Paths
               WHERE Paths.ApplicationId = @ApplicationId
                      AND PerUser.PathId = Paths.PathId
                      AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              ) AS UserDataPerPath
              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
             )
        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
        ORDER BY Paths.Path ASC

        SELECT @TotalRecords = @@ROWCOUNT

        SELECT Paths.Path,
               SharedDataPerPath.LastUpdatedDate,
               SharedDataPerPath.SharedDataLength,
               UserDataPerPath.UserDataLength,
               UserDataPerPath.UserCount
        FROM dbo.aspnet_Paths Paths,
             ((SELECT PageIndex.ItemId AS PathId,
                      AllUsers.LastUpdatedDate AS LastUpdatedDate,
                      DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
               FROM dbo.aspnet_PersonalizationAllUsers AllUsers, #PageIndex PageIndex
               WHERE AllUsers.PathId = PageIndex.ItemId
                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
              ) AS SharedDataPerPath
              FULL OUTER JOIN
              (SELECT PageIndex.ItemId AS PathId,
                      SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
                      COUNT(*) AS UserCount
               FROM aspnet_PersonalizationPerUser PerUser, #PageIndex PageIndex
               WHERE PerUser.PathId = PageIndex.ItemId
                     AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
               GROUP BY PageIndex.ItemId
              ) AS UserDataPerPath
              ON SharedDataPerPath.PathId = UserDataPerPath.PathId
             )
        WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
        ORDER BY Paths.Path ASC
    END
    ELSE
    BEGIN
        -- Insert into our temp table
        INSERT INTO #PageIndex (ItemId)
        SELECT PerUser.Id
        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
        WHERE Paths.ApplicationId = @ApplicationId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
              AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
              AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
        ORDER BY Paths.Path ASC, Users.UserName ASC

        SELECT @TotalRecords = @@ROWCOUNT

        SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
        FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths, #PageIndex PageIndex
        WHERE PerUser.Id = PageIndex.ItemId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
        ORDER BY Paths.Path ASC, Users.UserName ASC
    END

    RETURN @TotalRecords
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationAdministration_GetCountOfState]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_GetCountOfState] (
    @Count int OUT,
    @AllUsersScope bit,
    @ApplicationName NVARCHAR(256),
    @Path NVARCHAR(256) = NULL,
    @UserName NVARCHAR(256) = NULL,
    @InactiveSinceDate DATETIME = NULL)
AS
BEGIN

    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
        IF (@AllUsersScope = 1)
            SELECT @Count = COUNT(*)
            FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
            WHERE Paths.ApplicationId = @ApplicationId
                  AND AllUsers.PathId = Paths.PathId
                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
        ELSE
            SELECT @Count = COUNT(*)
            FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
            WHERE Paths.ApplicationId = @ApplicationId
                  AND PerUser.UserId = Users.UserId
                  AND PerUser.PathId = Paths.PathId
                  AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
                  AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
                  AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationAdministration_ResetSharedState]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_ResetSharedState] (
    @Count int OUT,
    @ApplicationName NVARCHAR(256),
    @Path NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationAllUsers
        WHERE PathId IN
            (SELECT AllUsers.PathId
             FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
             WHERE Paths.ApplicationId = @ApplicationId
                   AND AllUsers.PathId = Paths.PathId
                   AND Paths.LoweredPath = LOWER(@Path))

        SELECT @Count = @@ROWCOUNT
    END
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationAdministration_ResetUserState]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAdministration_ResetUserState] (
    @Count                  int                 OUT,
    @ApplicationName        NVARCHAR(256),
    @InactiveSinceDate      DATETIME            = NULL,
    @UserName               NVARCHAR(256)       = NULL,
    @Path                   NVARCHAR(256)       = NULL)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
        SELECT @Count = 0
    ELSE
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationPerUser
        WHERE Id IN (SELECT PerUser.Id
                     FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
                     WHERE Paths.ApplicationId = @ApplicationId
                           AND PerUser.UserId = Users.UserId
                           AND PerUser.PathId = Paths.PathId
                           AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
                           AND (@UserName IS NULL OR Users.LoweredUserName = LOWER(@UserName))
                           AND (@Path IS NULL OR Paths.LoweredPath = LOWER(@Path)))

        SELECT @Count = @@ROWCOUNT
    END
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_GetPageSettings]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_GetPageSettings] (
    @ApplicationName  NVARCHAR(256),
    @Path              NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationAllUsers p WHERE p.PathId = @PathId
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_ResetPageSettings] (
    @ApplicationName  NVARCHAR(256),
    @Path              NVARCHAR(256))
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    DELETE FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId
    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationAllUsers_SetPageSettings]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationAllUsers_SetPageSettings] (
    @ApplicationName  NVARCHAR(256),
    @Path             NVARCHAR(256),
    @PageSettings     IMAGE,
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
    END

    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationAllUsers WHERE PathId = @PathId))
        UPDATE dbo.aspnet_PersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE PathId = @PathId
    ELSE
        INSERT INTO dbo.aspnet_PersonalizationAllUsers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)
    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationPerUser_GetPageSettings]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_GetPageSettings] (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        RETURN
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    SELECT p.PageSettings FROM dbo.aspnet_PersonalizationPerUser p WHERE p.PathId = @PathId AND p.UserId = @UserId
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationPerUser_ResetPageSettings]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_ResetPageSettings] (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
    IF (@ApplicationId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        RETURN
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        RETURN
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId
    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_PersonalizationPerUser_SetPageSettings]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_PersonalizationPerUser_SetPageSettings] (
    @ApplicationName  NVARCHAR(256),
    @UserName         NVARCHAR(256),
    @Path             NVARCHAR(256),
    @PageSettings     IMAGE,
    @CurrentTimeUtc   DATETIME)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    DECLARE @PathId UNIQUEIDENTIFIER
    DECLARE @UserId UNIQUEIDENTIFIER

    SELECT @ApplicationId = NULL
    SELECT @PathId = NULL
    SELECT @UserId = NULL

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    SELECT @PathId = u.PathId FROM dbo.aspnet_Paths u WHERE u.ApplicationId = @ApplicationId AND u.LoweredPath = LOWER(@Path)
    IF (@PathId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Paths_CreatePath @ApplicationId, @Path, @PathId OUTPUT
    END

    SELECT @UserId = u.UserId FROM dbo.aspnet_Users u WHERE u.ApplicationId = @ApplicationId AND u.LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
    BEGIN
        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
    END

    UPDATE   dbo.aspnet_Users WITH (ROWLOCK)
    SET      LastActivityDate = @CurrentTimeUtc
    WHERE    UserId = @UserId
    IF (@@ROWCOUNT = 0) -- Username not found
        RETURN

    IF (EXISTS(SELECT PathId FROM dbo.aspnet_PersonalizationPerUser WHERE UserId = @UserId AND PathId = @PathId))
        UPDATE dbo.aspnet_PersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc WHERE UserId = @UserId AND PathId = @PathId
    ELSE
        INSERT INTO dbo.aspnet_PersonalizationPerUser(UserId, PathId, PageSettings, LastUpdatedDate) VALUES (@UserId, @PathId, @PageSettings, @CurrentTimeUtc)
    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Profile_DeleteInactiveProfiles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Profile_DeleteInactiveProfiles]
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @InactiveSinceDate      datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT  0
        RETURN
    END

    DELETE
    FROM    dbo.aspnet_Profile
    WHERE   UserId IN
            (   SELECT  UserId
                FROM    dbo.aspnet_Users u
                WHERE   ApplicationId = @ApplicationId
                        AND (LastActivityDate <= @InactiveSinceDate)
                        AND (
                                (@ProfileAuthOptions = 2)
                             OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                             OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                            )
            )

    SELECT  @@ROWCOUNT
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Profile_DeleteProfiles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Profile_DeleteProfiles]
    @ApplicationName        nvarchar(256),
    @UserNames              nvarchar(4000)
AS
BEGIN
    DECLARE @UserName     nvarchar(256)
    DECLARE @CurrentPos   int
    DECLARE @NextPos      int
    DECLARE @NumDeleted   int
    DECLARE @DeletedUser  int
    DECLARE @TranStarted  bit
    DECLARE @ErrorCode    int

    SET @ErrorCode = 0
    SET @CurrentPos = 1
    SET @NumDeleted = 0
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    WHILE (@CurrentPos <= LEN(@UserNames))
    BEGIN
        SELECT @NextPos = CHARINDEX(N',', @UserNames,  @CurrentPos)
        IF (@NextPos = 0 OR @NextPos IS NULL)
            SELECT @NextPos = LEN(@UserNames) + 1

        SELECT @UserName = SUBSTRING(@UserNames, @CurrentPos, @NextPos - @CurrentPos)
        SELECT @CurrentPos = @NextPos+1

        IF (LEN(@UserName) > 0)
        BEGIN
            SELECT @DeletedUser = 0
            EXEC dbo.aspnet_Users_DeleteUser @ApplicationName, @UserName, 4, @DeletedUser OUTPUT
            IF( @@ERROR <> 0 )
            BEGIN
                SET @ErrorCode = -1
                GOTO Cleanup
            END
            IF (@DeletedUser <> 0)
                SELECT @NumDeleted = @NumDeleted + 1
        END
    END
    SELECT @NumDeleted
    IF (@TranStarted = 1)
    BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
    END
    SET @TranStarted = 0

    RETURN 0

Cleanup:
    IF (@TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END
    RETURN @ErrorCode
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Profile_GetNumberOfInactiveProfiles]
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @InactiveSinceDate      datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
    BEGIN
        SELECT 0
        RETURN
    END

    SELECT  COUNT(*)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
    WHERE   ApplicationId = @ApplicationId
        AND u.UserId = p.UserId
        AND (LastActivityDate <= @InactiveSinceDate)
        AND (
                (@ProfileAuthOptions = 2)
                OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
            )
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Profile_GetProfiles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Profile_GetProfiles]
    @ApplicationName        nvarchar(256),
    @ProfileAuthOptions     int,
    @PageIndex              int,
    @PageSize               int,
    @UserNameToMatch        nvarchar(256) = NULL,
    @InactiveSinceDate      datetime      = NULL
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT  u.UserId
        FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p
        WHERE   ApplicationId = @ApplicationId
            AND u.UserId = p.UserId
            AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
            AND (     (@ProfileAuthOptions = 2)
                   OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
                   OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
                 )
            AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
        ORDER BY UserName

    SELECT  u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
            DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
    FROM    dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
    WHERE   u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound

    SELECT COUNT(*)
    FROM   #PageIndexForUsers

    DROP TABLE #PageIndexForUsers
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Profile_GetProperties]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Profile_GetProperties]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
    FROM         dbo.aspnet_Profile
    WHERE        UserId = @UserId

    IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate=@CurrentTimeUtc
        WHERE  UserId = @UserId
    END
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Profile_SetProperties]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Profile_SetProperties]
    @ApplicationName        nvarchar(256),
    @PropertyNames          ntext,
    @PropertyValuesString   ntext,
    @PropertyValuesBinary   image,
    @UserName               nvarchar(256),
    @IsUserAnonymous        bit,
    @CurrentTimeUtc         datetime
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
       BEGIN TRANSACTION
       SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    DECLARE @UserId uniqueidentifier
    DECLARE @LastActivityDate datetime
    SELECT  @UserId = NULL
    SELECT  @LastActivityDate = @CurrentTimeUtc

    SELECT @UserId = UserId
    FROM   dbo.aspnet_Users
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)
    IF (@UserId IS NULL)
        EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    UPDATE dbo.aspnet_Users
    SET    LastActivityDate=@CurrentTimeUtc
    WHERE  UserId = @UserId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF (EXISTS( SELECT *
               FROM   dbo.aspnet_Profile
               WHERE  UserId = @UserId))
        UPDATE dbo.aspnet_Profile
        SET    PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString,
               PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc
        WHERE  UserId = @UserId
    ELSE
        INSERT INTO dbo.aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate)
             VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
    	SET @TranStarted = 0
    	COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_RegisterSchemaVersion]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_RegisterSchemaVersion]
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128),
    @IsCurrentVersion          bit,
    @RemoveIncompatibleSchema  bit
AS
BEGIN
    IF( @RemoveIncompatibleSchema = 1 )
    BEGIN
        DELETE FROM dbo.aspnet_SchemaVersions WHERE Feature = LOWER( @Feature )
    END
    ELSE
    BEGIN
        IF( @IsCurrentVersion = 1 )
        BEGIN
            UPDATE dbo.aspnet_SchemaVersions
            SET IsCurrentVersion = 0
            WHERE Feature = LOWER( @Feature )
        END
    END

    INSERT  dbo.aspnet_SchemaVersions( Feature, CompatibleSchemaVersion, IsCurrentVersion )
    VALUES( LOWER( @Feature ), @CompatibleSchemaVersion, @IsCurrentVersion )
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Roles_CreateRole]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Roles_CreateRole]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF (EXISTS(SELECT RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId))
    BEGIN
        SET @ErrorCode = 1
        GOTO Cleanup
    END

    INSERT INTO dbo.aspnet_Roles
                (ApplicationId, RoleName, LoweredRoleName)
         VALUES (@ApplicationId, @RoleName, LOWER(@RoleName))

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN(0)

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Roles_DeleteRole]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Roles_DeleteRole]
    @ApplicationName            nvarchar(256),
    @RoleName                   nvarchar(256),
    @DeleteOnlyIfRoleIsEmpty    bit
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

    DECLARE @ErrorCode     int
    SET @ErrorCode = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1
    END
    ELSE
        SET @TranStarted = 0

    DECLARE @RoleId   uniqueidentifier
    SELECT  @RoleId = NULL
    SELECT  @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
    BEGIN
        SELECT @ErrorCode = 1
        GOTO Cleanup
    END
    IF (@DeleteOnlyIfRoleIsEmpty <> 0)
    BEGIN
        IF (EXISTS (SELECT RoleId FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId))
        BEGIN
            SELECT @ErrorCode = 2
            GOTO Cleanup
        END
    END


    DELETE FROM dbo.aspnet_UsersInRoles  WHERE @RoleId = RoleId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    DELETE FROM dbo.aspnet_Roles WHERE @RoleId = RoleId  AND ApplicationId = @ApplicationId

    IF( @@ERROR <> 0 )
    BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
    END

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
    END

    RETURN(0)

Cleanup:

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Roles_GetAllRoles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Roles_GetAllRoles] (
    @ApplicationName           nvarchar(256))
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN
    SELECT RoleName
    FROM   dbo.aspnet_Roles WHERE ApplicationId = @ApplicationId
    ORDER BY RoleName
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Roles_RoleExists]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Roles_RoleExists]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(0)
    IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId ))
        RETURN(1)
    ELSE
        RETURN(0)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Setup_RemoveAllRoleMembers]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers]
    @name   sysname
AS
BEGIN
    CREATE TABLE #aspnet_RoleMembers
    (
        Group_name      sysname,
        Group_id        smallint,
        Users_in_group  sysname,
        User_id         smallint
    )

    INSERT INTO #aspnet_RoleMembers
    EXEC sp_helpuser @name

    DECLARE @user_id smallint
    DECLARE @cmd nvarchar(500)
    DECLARE c1 cursor FORWARD_ONLY FOR
        SELECT User_id FROM #aspnet_RoleMembers

    OPEN c1

    FETCH c1 INTO @user_id
    WHILE (@@fetch_status = 0)
    BEGIN
        SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + ''''
        EXEC (@cmd)
        FETCH c1 INTO @user_id
    END

    CLOSE c1
    DEALLOCATE c1
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Setup_RestorePermissions]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Setup_RestorePermissions]
    @name   sysname
AS
BEGIN
    DECLARE @object sysname
    DECLARE @protectType char(10)
    DECLARE @action varchar(60)
    DECLARE @grantee sysname
    DECLARE @cmd nvarchar(500)
    DECLARE c1 cursor FORWARD_ONLY FOR
        SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name

    OPEN c1

    FETCH c1 INTO @object, @protectType, @action, @grantee
    WHILE (@@fetch_status = 0)
    BEGIN
        SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
        EXEC (@cmd)
        FETCH c1 INTO @object, @protectType, @action, @grantee
    END

    CLOSE c1
    DEALLOCATE c1
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_UnRegisterSchemaVersion]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_UnRegisterSchemaVersion]
    @Feature                   nvarchar(128),
    @CompatibleSchemaVersion   nvarchar(128)
AS
BEGIN
    DELETE FROM dbo.aspnet_SchemaVersions
        WHERE   Feature = LOWER(@Feature) AND @CompatibleSchemaVersion = CompatibleSchemaVersion
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Users_CreateUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_Users_CreateUser]
    @ApplicationId    uniqueidentifier,
    @UserName         nvarchar(256),
    @IsUserAnonymous  bit,
    @LastActivityDate DATETIME,
    @UserId           uniqueidentifier OUTPUT
AS
BEGIN
    IF( @UserId IS NULL )
        SELECT @UserId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
                    WHERE @UserId = UserId ) )
            RETURN -1
    END

    INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
    VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)

    RETURN 0
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_Users_DeleteUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @TablesToDeleteFrom int,
    @NumTablesDeletedFrom int OUTPUT
AS
BEGIN
    DECLARE @UserId               uniqueidentifier
    SELECT  @UserId               = NULL
    SELECT  @NumTablesDeletedFrom = 0

    DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
	SET @TranStarted = 0

    DECLARE @ErrorCode   int
    DECLARE @RowCount    int

    SET @ErrorCode = 0
    SET @RowCount  = 0

    SELECT  @UserId = u.UserId
    FROM    dbo.aspnet_Users u, dbo.aspnet_Applications a
    WHERE   u.LoweredUserName       = LOWER(@UserName)
        AND u.ApplicationId         = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@UserId IS NULL)
    BEGIN
        GOTO Cleanup
    END

    -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (type = 'V'))))
    BEGIN
        DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
               @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
    IF ((@TablesToDeleteFrom & 2) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_UsersInRoles') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
    IF ((@TablesToDeleteFrom & 4) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_Profiles') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
    IF ((@TablesToDeleteFrom & 8) <> 0  AND
        (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_WebPartState_User') AND (type = 'V'))) )
    BEGIN
        DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
    IF ((@TablesToDeleteFrom & 1) <> 0 AND
        (@TablesToDeleteFrom & 2) <> 0 AND
        (@TablesToDeleteFrom & 4) <> 0 AND
        (@TablesToDeleteFrom & 8) <> 0 AND
        (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
    BEGIN
        DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId

        SELECT @ErrorCode = @@ERROR,
                @RowCount = @@ROWCOUNT

        IF( @ErrorCode <> 0 )
            GOTO Cleanup

        IF (@RowCount <> 0)
            SELECT  @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
    END

    IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0

Cleanup:
    SET @NumTablesDeletedFrom = 0

    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
	    ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_AddUsersToRoles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
	@ApplicationName  nvarchar(256),
	@UserNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000),
	@CurrentTimeUtc   datetime
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)
	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num		int
	DECLARE @Pos		int
	DECLARE @NextPos	int
	DECLARE @Name		nvarchar(256)

	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		SELECT TOP 1 Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END

	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1

	WHILE(@Pos <= LEN(@UserNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @UserNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@UserNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbUsers
	  SELECT UserId
	  FROM   dbo.aspnet_Users ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

	IF (@@ROWCOUNT <> @Num)
	BEGIN
		DELETE FROM @tbNames
		WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)

		INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
		  SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
		  FROM   @tbNames

		INSERT INTO @tbUsers
		  SELECT  UserId
		  FROM	dbo.aspnet_Users au, @tbNames t
		  WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
	END

	IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
	BEGIN
		SELECT TOP 1 UserName, RoleName
		FROM		 dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
		WHERE		u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
	SELECT UserId, RoleId
	FROM @tbUsers, @tbRoles

	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_FindUsersInRole]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_FindUsersInRole]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256),
    @UserNameToMatch  nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
     DECLARE @RoleId uniqueidentifier
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.aspnet_Roles
     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.UserName
    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId AND LoweredUserName LIKE LOWER(@UserNameToMatch)
    ORDER BY u.UserName
    RETURN(0)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_GetRolesForUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_GetRolesForUser]
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT  @UserId = UserId
    FROM    dbo.aspnet_Users
    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

    IF (@UserId IS NULL)
        RETURN(1)

    SELECT r.RoleName
    FROM   dbo.aspnet_Roles r, dbo.aspnet_UsersInRoles ur
    WHERE  r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId AND ur.UserId = @UserId
    ORDER BY r.RoleName
    RETURN (0)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_GetUsersInRoles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_GetUsersInRoles]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)
     DECLARE @RoleId uniqueidentifier
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.aspnet_Roles
     WHERE   LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.UserName
    FROM   dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur
    WHERE  u.UserId = ur.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
    ORDER BY u.UserName
    RETURN(0)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(2)
    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL
    DECLARE @RoleId uniqueidentifier
    SELECT  @RoleId = NULL

    SELECT  @UserId = UserId
    FROM    dbo.aspnet_Users
    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

    IF (@UserId IS NULL)
        RETURN(2)

    SELECT  @RoleId = RoleId
    FROM    dbo.aspnet_Roles
    WHERE   LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @ApplicationId

    IF (@RoleId IS NULL)
        RETURN(3)

    IF (EXISTS( SELECT * FROM dbo.aspnet_UsersInRoles WHERE  UserId = @UserId AND RoleId = @RoleId))
        RETURN(1)
    ELSE
        RETURN(0)
END
GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
	@ApplicationName  nvarchar(256),
	@UserNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000)
AS
BEGIN
	DECLARE @AppId uniqueidentifier
	SELECT  @AppId = NULL
	SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
	IF (@AppId IS NULL)
		RETURN(2)


	DECLARE @TranStarted   bit
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 )
	BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END

	DECLARE @tbNames  table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles  table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbUsers  table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num	  int
	DECLARE @Pos	  int
	DECLARE @NextPos  int
	DECLARE @Name	  nvarchar(256)
	DECLARE @CountAll int
	DECLARE @CountU	  int
	DECLARE @CountR	  int


	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbRoles
	  SELECT RoleId
	  FROM   dbo.aspnet_Roles ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
	SELECT @CountR = @@ROWCOUNT

	IF (@CountR <> @Num)
	BEGIN
		SELECT TOP 1 N'', Name
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar,  @tbRoles r WHERE r.RoleId = ar.RoleId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(2)
	END


	DELETE FROM @tbNames WHERE 1=1
	SET @Num = 0
	SET @Pos = 1


	WHILE(@Pos <= LEN(@UserNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @UserNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@UserNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

	INSERT INTO @tbUsers
	  SELECT UserId
	  FROM   dbo.aspnet_Users ar, @tbNames t
	  WHERE  LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

	SELECT @CountU = @@ROWCOUNT
	IF (@CountU <> @Num)
	BEGIN
		SELECT TOP 1 Name, N''
		FROM   @tbNames
		WHERE  LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE u.UserId = au.UserId)

		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(1)
	END

	SELECT  @CountAll = COUNT(*)
	FROM	dbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
	WHERE   ur.UserId = u.UserId AND ur.RoleId = r.RoleId

	IF (@CountAll <> @CountU * @CountR)
	BEGIN
		SELECT TOP 1 UserName, RoleName
		FROM		 @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
		WHERE		 u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
					 tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
					 tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
		IF( @TranStarted = 1 )
			ROLLBACK TRANSACTION
		RETURN(3)
	END

	DELETE FROM dbo.aspnet_UsersInRoles
	WHERE UserId IN (SELECT UserId FROM @tbUsers)
	  AND RoleId IN (SELECT RoleId FROM @tbRoles)
	IF( @TranStarted = 1 )
		COMMIT TRANSACTION
	RETURN(0)
END
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
GO
/****** Object:  StoredProcedure [dbo].[aspnet_WebEvent_LogEvent]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[aspnet_WebEvent_LogEvent]
        @EventId         char(32),
        @EventTimeUtc    datetime,
        @EventTime       datetime,
        @EventType       nvarchar(256),
        @EventSequence   decimal(19,0),
        @EventOccurrence decimal(19,0),
        @EventCode       int,
        @EventDetailCode int,
        @Message         nvarchar(1024),
        @ApplicationPath nvarchar(256),
        @ApplicationVirtualPath nvarchar(256),
        @MachineName    nvarchar(256),
        @RequestUrl      nvarchar(1024),
        @ExceptionType   nvarchar(256),
        @Details         ntext
AS
BEGIN
    INSERT
        dbo.aspnet_WebEvent_Events
        (
            EventId,
            EventTimeUtc,
            EventTime,
            EventType,
            EventSequence,
            EventOccurrence,
            EventCode,
            EventDetailCode,
            Message,
            ApplicationPath,
            ApplicationVirtualPath,
            MachineName,
            RequestUrl,
            ExceptionType,
            Details
        )
    VALUES
    (
        @EventId,
        @EventTimeUtc,
        @EventTime,
        @EventType,
        @EventSequence,
        @EventOccurrence,
        @EventCode,
        @EventDetailCode,
        @Message,
        @ApplicationPath,
        @ApplicationVirtualPath,
        @MachineName,
        @RequestUrl,
        @ExceptionType,
        @Details
    )
END
GO
/****** Object:  StoredProcedure [dbo].[sp_AddNewGroup]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Samson
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_AddNewGroup]

@GroupName varchar(50),
@Desc varchar(100),
@IsActive int

AS
BEGIN

insert into tbl_GroupsMaster(vc_GroupName, vc_Description,int_IsActive) values (@GroupName, @Desc, @IsActive)

END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetAllUsers]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetAllUsers]
	
AS
BEGIN
select UM.UserId,UM.UserName  from aspnet_Users UM
END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetAllUsersorParticularUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetAllUsersorParticularUser]

@GroupId int=null,
@StudentID int=null,
@StudentName varchar(50)= null
AS
BEGIN
declare @Gid int=null
declare @StudID int=null
declare @StudName varchar(50)= null
set @Gid= @GroupId
set @StudID = @StudentID
set @StudName= @StudentName

CREATE TABLE #USERINFO
	(
		USERID UNIQUEIDENTIFIER,
		USERNAME VARCHAR(50),
		GroupID int
	)
		
INSERT INTO #USERINFO
SELECT UM.UserId,UM.UserName,UG.GroupID from aspnet_Users UM inner join tbl_UserGroups UG on UM.UserID = UG.UserID


if (@Gid=0 and @StudName='0')
begin
	select distinct userid, USERNAME  from aspnet_Users
end
else if (@Gid = 0 and @StudName= @StudentName)
begin
	select distinct userid, USERNAME from aspnet_Users where USERNAME like @StudName
end

else if (@Gid = @GroupId and @StudName= @StudentName)
begin
	select distinct userid,USERNAME  from #USERINFO where GroupID = 
	@Gid and USERNAME like @StudName

end

else if (@Gid = @GroupId and @StudName='0')
begin
	select distinct userid, USERNAME FROM #USERINFO where GroupID = 
	@Gid
end

else if (@Gid = 0)
begin
	select distinct userid, USERNAME from aspnet_Users
end

else if (@Gid = @GroupId)
begin
	select distinct userid, USERNAME from #USERINFO where GroupID = 
	@Gid
end


END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetAnswers]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<ANKUR>
-- Create date: <15/3/2011>
-- Description:	<Displying Answers for questions>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetAnswers] 
@QuestionID INT = null,
@TransType int
AS
BEGIN
	
	IF(@TransType=1)--FOR RETRIVING ANSWER FOR A PARTICULAR QUESTION
	SELECT Answer,Justification from tbl_QuestionMaster WHERE QuestionId = @QuestionID
	
	ELSE IF(@TransType=2)--FOR RETRIVING RIGHT OPTION IDS FOR A PARTICULAR QUESTION
	BEGIN
	create table #temp
	(
	id int primary key identity(1,1),
	options varchar(50)
	)

	

	declare @ans varchar(50)
	declare @count int
	declare @i int
	DECLARE @option VARCHAR(50)
	DECLARE @optionId VARCHAR(50)

	set @ans=(select QM.Answer from tbl_QuestionMaster QM where QM.QuestionId=@QuestionID)

	insert into #temp
	select * from Mysplitfunction(@ans,'|')

	

	set @count=(select COUNT(id) from #temp)
	set @i=1
	SET @optionId=''

	while(@i<=@count)
	begin
	SET @option=(select QOM.OptionId
	from tbl_QuestionOptionMaster QOM inner join tbl_QuestionMaster QM on QM.QuestionId=QOM.QuestionId inner join  #temp t
	on QOM.Options=t.options
	where   t.id=@i and QM.QuestionId=@QuestionID)
	IF(@count=1)
	BEGIN
		SET @optionId=@optionId+@option
	END
	ELSE
	BEGIN
		SET @optionId=@optionId+@option+'|'
	END
	set @i=@i+1
	end


	select @optionId as 'Answer'
		
	END
   
END

GO
/****** Object:  StoredProcedure [dbo].[sp_GetGroup]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetGroup]
	
AS
BEGIN

	select  int_GroupID, vc_GroupName from tbl_GroupsMaster 
END



GO
/****** Object:  StoredProcedure [dbo].[sp_GetOptions]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<vijaya,Jansi>
-- Create date: <7-3-2011>
-- Description:	<Displying Options for questions>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetOptions] 
@QuestionID INT = null
AS
BEGIN
	
	SET NOCOUNT ON
	
	SELECT OptionId, Options, QuestionId from tbl_QuestionOptionMaster WHERE QuestionId = @QuestionID
   
END

--EXEC sp_GetOptions 11

GO
/****** Object:  StoredProcedure [dbo].[sp_GetQuestionBySubjectTopic]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ankur
-- Create date: 5/3/2011
-- Description:	shows the list of all question pertaning to subject and topic
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetQuestionBySubjectTopic]
@subjectId int=null,
@topicId Varchar(500)=null
AS
BEGIN

declare @count int
declare @i int
create table #tmp(
id int primary key identity(1,1),
MyString varchar(500)
)

create table #temp(
question varchar(5000),
priority varchar(20),
QuestionId int
)

insert into #tmp
select *from Mysplitfunction(@topicId,'|')
set @count=(select COUNT(id) from #tmp)
set @i=1
while(@i<=@count)
begin
declare @topic varchar(500)
set @topic=(select #tmp.MyString from #tmp where #tmp.id=@i)
insert into #temp
select QM.Question,QM.priority,QM.QuestionId  
from tbl_QuestionMaster QM where QM.SubjectId=@subjectId and QM.TopicId=@topic and QM.IsActive=1
set @i=@i+1
end
select #temp.question,#temp.priority,#temp.QuestionId from #temp
	
END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetSearchedTask]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		kavitha
-- Create date: <Create Date,,>
-- Description:	to search the tasks based on user
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSearchedTask]
@UserId varchar(200)=Null,
@TaskTypeId int =Null,
@Status int =null	
AS
BEGIN
	CREATE TABLE #TempUser(
ID INT PRIMARY KEY IDENTITY(1,1),
USERID UNIQUEIDENTIFIER
)
 CREATE TABLE #tempid(
	id int primary key identity(1,1),
	MyString varchar(1000),
	taskid int
	
	)
CREATE TABLE #temptask(
	TaskID int,TaskName varchar(50),TaskDesc varchar(100),TaskType varchar(50),vc_FirstName varchar(50),TestName varchar(50)
	,SubjectName varchar(50),topicname varchar(500),IsVerificationRequired int,LibraryID int,StartDate smalldatetime,EndDate smalldatetime ,status int
	
	)
	CREATE TABLE #countid(
	
	value int
	)
DECLARE @Cnt INT
DECLARE @J INT
DECLARE @l INT
DECLARE @id INT

DECLARE @Users UNIQUEIDENTIFIER
DECLARE @topicname varchar(1000)

INSERT INTO #TempUser
SELECT * FROM Mysplitfunction(@UserId,'|')

 

SET @Cnt=(SELECT COUNT(ID) FROM #TempUser)
SET @J=1
WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT CAST(T.USERID as int) FROM #TempUser T WHERE T.ID=@J)

INSERT INTO #tempid SELECT TopicID,TM.TaskID FROM tbl_TaskMaster TM inner join tbl_TaskTransactions TT  ON TM.TaskID=TT.TaskID WHERE TT.UserID=@Users
    
	DECLARE @val int=NUll
	SET @val=(SELECT COUNT(id) from #tempid WHERE #tempid.MyString is not null )
	INSERT INTO #countid select @val
	SELECT * FROM #countid 
    
	BEGIN
    DECLARE @tasktype varchar(50)
    SET @tasktype =(SELECT TTM.TaskName FROM #tempid,tbl_TaskMaster TM inner join tbl_TaskTypeMaster TTM ON TTM.TaskTypeId=TM.TaskTypeID WHERE  #tempid.id=@J AND #tempid.taskid=TM.TaskID )
    
    if(@tasktype='Assignment')
    
    BEGIN
       DECLARE @topicid varchar(1000)
       
	   SET @topicid=(select #tempid.MyString  from #tempid where #tempid.id=@J  )
       
       EXEC sp_GetTopicListsById @topicid,@topicname OUT
       
      BEGIN
      INSERT INTO #temptask 
      SELECT  TM.TaskID,TM.TaskName,TM.TaskDesc,TTM.TaskName as TaskType,UM.UserName,TLM.TestName,SM.SubjectName,@topicname,TM.IsVerificationRequired,TM.LibraryID,TM.StartDate,TM.EndDate,TM.status 
      FROM
      #tempid,
     
      tbl_TaskMaster TM inner join tbl_TaskTypeMaster TTM ON TTM.TaskTypeId=TM.TaskTypeID
      inner join tbl_TaskTransactions TTS ON TM.TaskID=TTS.TaskID
	  inner join aspnet_Users UM ON UM.UserID=TM.TaskOwnerID
      inner join tbl_TestListMaster TLM ON TLM.TestId=TM.TestID
      inner join tbl_SubjectMaster SM ON SM.SubjectId=TM.SubjectID
       WHERE #tempid.id=@J  and #tempid.taskid=TM.TaskID and TTS.UserID=@Users
      END
      END
      else
      BEGIN
      INSERT INTO #temptask 
      SELECT  TM.TaskID,TM.TaskName,TM.TaskDesc,TTM.TaskName as TaskType,UM.UserName,TLM.TestName,'Null' as SubjectName,'Null' as topicname,TM.IsVerificationRequired,TM.LibraryID,TM.StartDate,TM.EndDate,TM.status 
      FROM
      #tempid,
      tbl_TaskMaster TM inner join tbl_TaskTypeMaster TTM ON TTM.TaskTypeId=TM.TaskTypeID
      inner join tbl_TaskTransactions TTS ON TM.TaskID=TTS.TaskID
	  inner join aspnet_Users UM ON UM.UserID=TM.TaskOwnerID
      inner join tbl_TestListMaster TLM ON TLM.TestId=TM.TestID
      WHERE #tempid.id=@J and #tempid.taskid=TM.TaskID and TTS.UserID=@Users
      END
      
     END
      SET @J=@J+1
       
      
END
if(@UserId!=0 and @TaskTypeId!=0 and @Status!=0)
BEGIN
      SELECT TT.TaskID,TT.TaskName,TT.TaskDesc,TT.TaskType,TT.vc_FirstName,TT.TestName,TT.SubjectName,TT.topicname,TT.IsVerificationRequired,TT.LibraryID,TT.StartDate,TT.EndDate,TT.status  
      FROM #temptask TT
       inner join tbl_TaskTypeMaster TTM ON TTM.TaskName=TT.TaskType 
      
      WHERE TTM.TaskTypeId=@TaskTypeId and TT.status=@Status
END
END

GO
/****** Object:  StoredProcedure [dbo].[SP_GetSetAssignment]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		ANKUR BHATNAGAR
-- Create date: 24/3/2011
-- Description:	FOR INSERTING NEW ASSIGNMENT,EDITING ASSIGNMENT
-- =============================================
CREATE PROCEDURE [dbo].[SP_GetSetAssignment]
@AssignmentId INT=NULL,
@AssignmentName VARCHAR(50)=NULL,
@SubjectId INT=NULL,
@TopicId INT=NULL,
@Question VARCHAR(50)=NULL,
@Attachment VARCHAR(50)=NULL,
@StartDate DATETIME=NULL,
@EndDate DATETIME=NULL,
@Status VARCHAR(50)=NULL,
@Notify INT=NULL,
@UserId VARCHAR(500)=NULL,
@Remarks VARCHAR(50)=NULL,
@TransType INT
AS
BEGIN
IF(@TransType=1)--FOR INSERTING NEW ASSIGNMENT INFORMATION
BEGIN
CREATE TABLE #TEMP(
ID INT PRIMARY KEY IDENTITY(1,1),
USERID UNIQUEIDENTIFIER
)
DECLARE @AssId INT
DECLARE @Count INT
DECLARE @i INT


INSERT INTO tbl_AssignmentMaster(AssignmentName,SubjectId,TopicId,Question,Attachment,StartDate,
            EndDate,States,Notify) VALUES(@AssignmentName,
            @SubjectId,@TopicId,@Question,@Attachment,@StartDate,@EndDate,@Status,@Notify)
SET @AssId=@@IDENTITY

INSERT INTO #TEMP (USERID)
SELECT * FROM Mysplitfunction(@UserId,'|')

SET @Count=(SELECT COUNT(ID) FROM #TEMP)
SET @i=1

WHILE(@i<=@Count)
BEGIN
DECLARE @User UNIQUEIDENTIFIER
SET @User=(SELECT T.USERID FROM #TEMP T WHERE T.ID=@i)
INSERT INTO tbl_AssignmentTrans(AssignmentId,UserId,States) VALUES (@AssId,@User,@Status)
SET @i=@i+1
END
END

ELSE IF(@TransType=2)--FOR SEARCHING ASSIGNMENT INFORMATION ON BASIS OF SUBJECT,TOPIC,STATUS AND USERID 
BEGIN

CREATE TABLE #TempUser(
ID INT PRIMARY KEY IDENTITY(1,1),
USERID UNIQUEIDENTIFIER
)

CREATE TABLE #TempAssignTrans(
AssignmentId INT,
AssignmentName VARCHAR(50),
SubjectName VARCHAR(50),
TopicName VARCHAR(50),
Question VARCHAR(50),
Attachment VARCHAR(50),
StartDate DATETIME,
EndDate DATETIME,
States VARCHAR(50),
Notify INT,
UserName VARCHAR(50),
)
DECLARE @Cnt INT
DECLARE @J INT
DECLARE @Users UNIQUEIDENTIFIER

INSERT INTO #TempUser
SELECT * FROM Mysplitfunction(@UserId,'|')


SET @Cnt=(SELECT COUNT(ID) FROM #TempUser)
SET @J=1

IF ((@SubjectId!=0) AND(@TopicId !=0) AND (@Status !='0'))
BEGIN

WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT T.USERID FROM #TempUser T WHERE T.ID=@J)
INSERT INTO #TempAssignTrans(AssignmentId,AssignmentName,SubjectName,TopicName,Question,StartDate,EndDate,UserName,States,Notify)
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,UM.UserName,AT.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.SubjectId=@SubjectId AND AM.TopicId=@TopicId AND AT.UserId=@Users AND AT.States=@Status

SET @J=@J+1
END

END

ELSE IF ((@SubjectId!=0) AND(@TopicId !=0) AND (@Status ='0'))
BEGIN

WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT T.USERID FROM #TempUser T WHERE T.ID=@J)
INSERT INTO #TempAssignTrans(AssignmentId,AssignmentName,SubjectName,TopicName,Question,StartDate,EndDate,UserName,States,Notify)
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,UM.UserName,AT.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.SubjectId=@SubjectId AND AM.TopicId=@TopicId AND AT.UserId=@Users AND AT.States=@Status

SET @J=@J+1
END

END

ELSE IF ((@SubjectId!=0) AND(@TopicId =0) AND (@Status !='0'))
BEGIN

WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT T.USERID FROM #TempUser T WHERE T.ID=@J)
INSERT INTO #TempAssignTrans(AssignmentId,AssignmentName,SubjectName,TopicName,Question,StartDate,EndDate,UserName,States,Notify)
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,UM.UserName,AT.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.SubjectId=@SubjectId AND AT.UserId=@Users AND AT.States=@Status

SET @J=@J+1
END

END


ELSE IF ((@SubjectId!=0) AND(@TopicId =0) AND (@Status ='0'))
BEGIN

WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT T.USERID FROM #TempUser T WHERE T.ID=@J)
INSERT INTO #TempAssignTrans(AssignmentId,AssignmentName,SubjectName,TopicName,Question,StartDate,EndDate,UserName,States,Notify)
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,UM.UserName,AT.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.SubjectId=@SubjectId  AND AT.UserId=@Users

SET @J=@J+1
END

END

ELSE IF ((@SubjectId=0) AND(@TopicId !=0) AND (@Status !='0'))
BEGIN

WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT T.USERID FROM #TempUser T WHERE T.ID=@J)
INSERT INTO #TempAssignTrans(AssignmentId,AssignmentName,SubjectName,TopicName,Question,StartDate,EndDate,UserName,States,Notify)
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,UM.UserName,AT.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.TopicId=@TopicId AND AT.UserId=@Users AND AT.States=@Status

SET @J=@J+1
END

END

ELSE IF ((@SubjectId=0) AND(@TopicId !=0) AND (@Status ='0'))
BEGIN

WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT T.USERID FROM #TempUser T WHERE T.ID=@J)
INSERT INTO #TempAssignTrans(AssignmentId,AssignmentName,SubjectName,TopicName,Question,StartDate,EndDate,UserName,States,Notify)
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,UM.UserName,AT.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON um.UserID=AT.UserId
WHERE AM.TopicId=@TopicId AND AT.UserId=@Users

SET @J=@J+1
END

END

ELSE IF ((@SubjectId=0) AND(@TopicId =0) AND (@Status !='0'))
BEGIN

WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT T.USERID FROM #TempUser T WHERE T.ID=@J)
INSERT INTO #TempAssignTrans(AssignmentId,AssignmentName,SubjectName,TopicName,Question,StartDate,EndDate,UserName,States,Notify)
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,UM.UserName,AT.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AT.UserId=@Users AND AT.States=@Status

SET @J=@J+1
END

END

ELSE IF ((@SubjectId=0) AND(@TopicId =0) AND (@Status ='0'))
BEGIN

WHILE(@J<=@Cnt)
BEGIN
SET @Users=(SELECT T.USERID FROM #TempUser T WHERE T.ID=@J)
INSERT INTO #TempAssignTrans(AssignmentId,AssignmentName,SubjectName,TopicName,Question,StartDate,EndDate,UserName,States,Notify)
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,UM.UserName,AT.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AT.UserId=@Users

SET @J=@J+1
END

END

SELECT * FROM #TempAssignTrans

END


ELSE IF(@TransType=3)--FOR SELECTING ALL ASSIGNMENT INFORMATION
BEGIN
SELECT AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.Attachment,AM.StartDate,AM.EndDate,AM.States,AM.Notify
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
WHERE AM.AssignmentId=@AssignmentId
SELECT AT.AssignmentId,AT.UserId,UM.UserName,AT.States,AT.AssignmentPath
FROM tbl_AssignmentTrans AT INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AT.AssignmentId=@AssignmentId 
END

ELSE IF(@TransType=4)--FOR UPDATING ASSIGNMENT INFORMATION
BEGIN
CREATE TABLE #TEMP1(
ID INT PRIMARY KEY IDENTITY(1,1),
USERID UNIQUEIDENTIFIER
)
DECLARE @AssignId INT
DECLARE @County INT
DECLARE @k INT

DELETE FROM tbl_AssignmentTrans WHERE AssignmentId=@AssignmentId

UPDATE tbl_AssignmentMaster
SET AssignmentName=@AssignmentName,SubjectId=@SubjectId,TopicId=@TopicId,Question=@Question,Attachment=@Attachment,
StartDate=@StartDate,EndDate=@EndDate,States=@Status,Notify=@Notify
WHERE AssignmentId=@AssignmentId


INSERT INTO #TEMP1
SELECT * FROM Mysplitfunction(@UserId,'|')

SET @County=(SELECT COUNT(ID) FROM #TEMP1)
SET @k=1

WHILE(@k<=@County)
BEGIN
DECLARE @User1 UNIQUEIDENTIFIER
SET @User1=(SELECT T.USERID FROM #TEMP1 T WHERE T.ID=@k)
INSERT INTO tbl_AssignmentTrans(AssignmentId,UserId,States) VALUES (@AssignmentId,@User1,@Status)
SET @k=@k+1
END


END

ELSE IF(@TransType=5)--FOR SELECTING ASSIGNMENT INFORMATION ON BASIS OF SUBJECT,TOPIC AND STATUS
BEGIN
IF ((@SubjectId!=0) AND(@TopicId !=0) AND (@Status !='0'))
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AM.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
WHERE AM.SubjectId=@SubjectId AND AM.TopicId=@TopicId AND AM.States=@Status
END

ELSE IF ((@SubjectId!=0) AND(@TopicId !=0) AND (@Status ='0'))
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AM.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
WHERE AM.SubjectId=@SubjectId AND AM.TopicId=@TopicId
END

ELSE IF ((@SubjectId!=0) AND(@TopicId =0) AND (@Status !='0'))
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AM.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
WHERE AM.SubjectId=@SubjectId AND  AM.States=@Status
END

ELSE IF ((@SubjectId!=0) AND(@TopicId =0) AND (@Status ='0'))
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AM.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
WHERE AM.SubjectId=@SubjectId
END

ELSE IF ((@SubjectId=0) AND(@TopicId !=0) AND (@Status !='0'))
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AM.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
WHERE AM.TopicId=@TopicId AND AM.States=@Status
END

ELSE IF ((@SubjectId=0) AND(@TopicId !=0) AND (@Status ='0'))
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AM.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
WHERE AM.TopicId=@TopicId
END

ELSE IF ((@SubjectId=0) AND(@TopicId =0) AND (@Status !='0'))
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AM.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
WHERE AM.States=@Status
END

ELSE IF ((@SubjectId=0) AND(@TopicId =0) AND (@Status ='0'))
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AM.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId 
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
END
END

ELSE IF(@TransType=6)--FOR SELECTING ASSIGNMENT TRANS ALL INFROMATION BASED ON ASSID
BEGIN
SELECT AT.AssignmentId,AT.UserId,UM.UserName AS 'userName',AT.States,AT.AssignmentPath
FROM tbl_AssignmentTrans AT INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AT.AssignmentId=@AssignmentId
END

ELSE IF(@TransType=7)--FOR SELECTING USER ASSIGNMENT INFORMATION BASED ON USERID AND ASSID
BEGIN
SELECT AT.AssignmentPath FROM tbl_AssignmentTrans AT WHERE AT.AssignmentId=@AssignmentId AND AT.UserId=@UserId
END

ELSE IF(@TransType=8)--FOR UPDATING USER STATUS WHEN CO-ORDINATOR CHECKS ASSIGNMENT 
BEGIN
DECLARE @CountStudent INT
DECLARE @CountStatus INT

UPDATE tbl_AssignmentTrans
SET States=@Status
WHERE UserId=@UserId AND AssignmentId=@AssignmentId
IF(@Remarks IS NOT NULL)
BEGIN
DECLARE @REM VARCHAR(50)
SET @REM='Shankar: '+@Remarks
INSERT INTO tbl_AssignmentTransHistory(AssignmentId,UserId,Remarks) VALUES (@AssignmentId,@UserId,@REM)
END

SET @CountStudent=(SELECT COUNT(AT.AssignmentId) FROM tbl_AssignmentTrans AT WHERE AT.AssignmentId=@AssignmentId)
SET @CountStatus=(SELECT COUNT(AT.AssignmentId) FROM tbl_AssignmentTrans AT WHERE AT.States='Submitted-Verified')
IF(@CountStatus=@CountStudent)
BEGIN
UPDATE tbl_AssignmentMaster 
SET States='Completed'
WHERE AssignmentId=@AssignmentId
END
END

ELSE IF (@TransType=9)--GET ASSIGNMENT HISTORY INFORMATION
BEGIN
SELECT AHT.Remarks FROM tbl_AssignmentTransHistory AHT WHERE AHT.AssignmentId=@AssignmentId AND AHT.UserId=@UserId
END

ELSE IF(@TransType=10)--GET ASSIGNMENT INFORMATION BASED ON PARTICULAR USER AND STATUS BUT DIFFRENT AND ALL SUB AND TOPICS
BEGIN

IF(@Status='Completed')
BEGIN
SET @Status='Submitted-Verified'
END

IF ((@SubjectId!=0) AND (@TopicId!=0))--FOR PARTICULAR SUBJECT AND PARTICULAR TOPIC
BEGIN
IF(@Status='Pending')
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AT.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId 
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.SubjectId=@SubjectId AND AM.TopicId=@TopicId AND AT.UserId=@UserId AND AT.States NOT IN('Scheduled','Submitted-Verified')
END
ELSE
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AT.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId 
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.SubjectId=@SubjectId AND AM.TopicId=@TopicId AND AT.States=@Status AND AT.UserId=@UserId
END
END

ELSE IF((@SubjectId!=0) AND (@TopicId=0))--FOR PARTICULAR SUBJECT BUT ALL TOPICS
BEGIN
IF(@Status='Pending')
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AT.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId 
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.SubjectId=@SubjectId AND AT.UserId=@UserId AND AT.States NOT IN('Scheduled','Submitted-Verified')
END
ELSE
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AT.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId 
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.SubjectId=@SubjectId AND AT.States=@Status AND AT.UserId=@UserId
END
END

ELSE IF((@SubjectId=0) AND (@TopicId!=0))--FOR ALL SUBJECT BUT PARTICULAR TOPICS
BEGIN
IF(@Status='Pending')
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AT.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId 
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.TopicId=@TopicId AND AT.UserId=@UserId AND AT.States NOT IN('Scheduled','Submitted-Verified')
END
ELSE
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AT.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId 
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AM.TopicId=@TopicId AND AT.States=@Status AND AT.UserId=@UserId
END
END

ELSE IF((@SubjectId=0) AND (@TopicId=0))--FOR ALL SUBJECT AND ALL TOPICS
BEGIN
IF(@Status='Pending')
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AT.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId 
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AT.UserId=@UserId AND AT.States NOT IN('Scheduled','Submitted-Verified')
END
ELSE
BEGIN
SELECT AM.AssignmentId,AM.AssignmentName,SM.SubjectName,TM.topicName,AM.Question,AM.StartDate,AM.EndDate,AT.States
FROM tbl_AssignmentMaster AM INNER JOIN tbl_AssignmentTrans AT ON AM.AssignmentId=AT.AssignmentId 
INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=AM.SubjectId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=AM.TopicId
INNER JOIN aspnet_Users UM ON UM.UserID=AT.UserId
WHERE AT.States=@Status AND AT.UserId=@UserId
END
END


END


ELSE IF(@TransType=11)--GET ASSIGNMENT SCHEDULED,COMPLETE AND PENDING COUNT INFORMATION BASED ON USERID
BEGIN
SELECT COUNT(AT.AssignmentId) FROM tbl_AssignmentTrans AT WHERE AT.States='Scheduled' AND AT.UserId=@UserId
SELECT COUNT(AT.AssignmentId) FROM tbl_AssignmentTrans AT WHERE AT.States NOT IN('Scheduled','Submitted-Verified') AND AT.UserId=@UserId
SELECT COUNT(AT.AssignmentId) FROM tbl_AssignmentTrans AT WHERE AT.States='Submitted-Verified' AND AT.UserId=@UserId
END

ELSE IF(@TransType=12)--FOR UPDATING USER STATUS WHEN USER SUBMIT ASSIGNMENT 
BEGIN
UPDATE tbl_AssignmentTrans
SET States='Submitted-Not Verified',AssignmentPath=@Attachment
WHERE UserId=@UserId AND AssignmentId=@AssignmentId
IF(@Remarks IS NOT NULL)
BEGIN
INSERT INTO tbl_AssignmentTransHistory(AssignmentId,UserId,Remarks) VALUES (@AssignmentId,@UserId,@Remarks)
END
END


END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetSetGroupsMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ANKUR BHATNAGAR
-- Create date: 04/04/2011
-- Description:	CREATE EDIT AND MANAGE GROUPS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetGroupsMaster]
@GroupID int = null,
@GroupName varchar(50)= null,
@Desc varchar(100)= null,
@IsActive int= null,
@userId varchar(5000)=null,
@TransType int
AS
BEGIN
IF(@TransType=1)--FOR SELECTING GROUP INFORMATION
BEGIN
SELECT int_GroupID AS "Group ID", vc_GroupName AS "Group Name",vc_Description AS "Description", int_IsActive AS "Active"  FROM tbl_GroupsMaster
END

IF(@TransType=2)--FOR SELECTING ALL USER INFORMATION
BEGIN
SELECT UM.UserId,UM.UserName FROM aspnet_Users UM
END

IF(@TransType=3)--FOR INSERTING NEW GROUP INFORMATION 
BEGIN
DECLARE @GID INT
INSERT INTO tbl_GroupsMaster(vc_GroupName,vc_Description,int_IsActive) VALUES(@GroupName,@Desc,@IsActive)
SET @GID=@@IDENTITY

create table #temp1(
id int primary key identity(1,1),
MyString uniqueidentifier
)

DECLARE @countId INT
DECLARE @k INT

insert into #temp1
select *from Mysplitfunction(@userId,'|')

set @countId=(select COUNT(id) from #temp1)
set @k=1
while(@k<=@countId)
begin
declare @useId uniqueidentifier
set @useId=(select #temp1.MyString from #temp1 where #temp1.id=@k)
insert into tbl_UserGroups(GroupID,UserID)VALUES(@GID,@useId)
set @k=@k+1
end

END

IF(@TransType=4)--FOR SELECTING PARTICULAR GROUP INFORMATION BASED ON GROUPID
BEGIN
SELECT int_GroupID AS "Group ID", vc_GroupName AS "Group Name",vc_Description AS "Description", int_IsActive AS "Active"  from tbl_GroupsMaster where int_GroupID = @GroupID

END

IF(@TransType=5)--FOR SELECTING PARTICULAR USER INFORMATION BASED ON GROUPID
BEGIN
SELECT UG.UserID FROM tbl_UserGroups UG WHERE UG.GroupID=@GroupID
END

IF(@TransType=6)--FOR INSERTING NEW GROUP INFORMATION 
BEGIN

UPDATE tbl_GroupsMaster 
SET  vc_GroupName=@GroupName,vc_Description=@Desc,int_IsActive=@IsActive
WHERE int_GroupID=@GroupID

DELETE tbl_UserGroups WHERE GroupID=@GroupID

create table #temp(
id int primary key identity(1,1),
MyString uniqueidentifier
)

DECLARE @count INT
DECLARE @J INT

insert into #temp
select *from Mysplitfunction(@userId,'|')

set @count=(select COUNT(id) from #temp)
set @J=1
while(@J<=@count)
begin
declare @usersId uniqueidentifier
set @usersId=(select #temp.MyString from #temp where #temp.id=@j)
insert into tbl_UserGroups(GroupID,UserID)VALUES(@GroupID,@usersId)
set @j=@j+1
end

END

END

GO
/****** Object:  StoredProcedure [dbo].[sp_GetSetSubjects]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ankur
-- Create date: 10 march 2011
-- Description:	for insert update and select subject information.
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetSubjects]
@subId INT=null,
@subName VARCHAR(50)=null,
@topicName VARCHAR(50)=null,
@newTopicName VARCHAR(50)=null,
@topicId VARCHAR(50)= null,
@isActive INT=null,
@TransType INT
AS
BEGIN
if (@TransType=0)--for selecting subName based on SubId
begin
select SM.SubjectName from tbl_SubjectMaster SM where SM.SubjectId=@subId
end

IF(@TransType=1)   -- For Selecting only Subject
BEGIN
SELECT SM.SubjectName,SM.SubjectId,SM.IsActive FROM tbl_SubjectMaster SM ORDER BY SM.SubjectName
END

ELSE IF(@TransType=2)  -- For Selecting both Subjects & Topics
begin
DECLARE @count INT
SET @count=(SELECT count(TM.TopicId)FROM tbl_TopicMaster TM WHERE TM.subjectId=@subId)
IF(@count>0)
	SELECT SM.SubjectName,TM.topicName AS 'topicName',TM.topicId,SM.IsActive FROM tbl_SubjectMaster SM inner join tbl_TopicMaster TM ON TM.subjectId=SM.SubjectId WHERE SM.SubjectId=@subId
ELSE
	SELECT SM.SubjectName,'null' as 'topicName',SM.IsActive FROM tbl_SubjectMaster SM WHERE SM.SubjectId=@subId
end
--------------------------------------------------	
if(@TransType=3)--for Insertion Subject And Topic
begin
declare @subjId int
declare @countI int
declare @i int

insert into tbl_SubjectMaster(SubjectName,IsActive) values(@subName,@isActive)

create table #tmp(
id int primary key identity(1,1),
MyString varchar(50)
)

insert into #tmp
select *from Mysplitfunction(@topicName,'|')
set @subjId=(select SM.SubjectId from tbl_SubjectMaster SM where SM.SubjectName=@subName)
set @countI=(select COUNT(id) from #tmp)
set @i=1
while(@i<=@countI)
begin
declare @topic varchar(50)
set @topic=(select #tmp.MyString from #tmp where #tmp.id=@i)
insert into tbl_TopicMaster(topicName,subjectId) values(@topic,@subjId)
set @i=@i+1
end
end

else if(@TransType=4)--for selection all subjects and topic
begin
select SM.SubjectId,SM.SubjectName,TM.topicName,SM.IsActive
from tbl_SubjectMaster SM inner join tbl_TopicMaster TM on SM.SubjectId=TM.subjectId
end		
--------------------------------------------------
else if(@TransType=5)--for updating subject name and topic names based on subjectid and topicid 
BEGIN

declare @countId int
declare @k int

create table #temp(
id int primary key identity(1,1),
topicId varchar(50),
)

create table #temp1(
id int primary key identity(1,1),
topicName varchar(50)
)

create table #temp2(
id int primary key identity(1,1),
topicId varchar(50),
topicName varchar(50)
)

create table #tempory(
id int primary key identity(1,1),
MyString varchar(50)
)

DECLARE @county int
DECLARE @a int

insert into #tempory
select *from Mysplitfunction(@newTopicName,'|')
set @county=(select COUNT(id) from #tempory)
set @a=1
while(@a<=@county)
begin
declare @topics varchar(50)
set @topics=(select #tempory.MyString from #tempory where #tempory.id=@a)
insert into tbl_TopicMaster(topicName,subjectId) values(@topics,@subId)
set @a=@a+1
end


insert into #temp
select *from Mysplitfunction(@topicId,'|')

INSERT INTO #temp1
select *from Mysplitfunction(@topicName,'|')

insert into #temp2(topicId,topicName)
select t.topicId,t1.topicName from #temp t inner join #temp1 t1 on t.id=t1.id


update tbl_SubjectMaster 
set SubjectName=@subName,IsActive=@isActive
where SubjectId=@subId

set @countId=(select COUNT(id) from #temp2)
set @k=1

declare @topName varchar(50)
declare @topId varchar(50)

while(@countId>=@k)
begin
set @topId=(select t2.topicId from #temp2 t2 where t2.id=@k)
set @topName=(select t2.topicName from #temp2 t2 where t2.id=@k)

update tbl_TopicMaster
set topicName=@topName
where topicId=@topId

set @k=@k+1
end


end


END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetSetTask]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:	 yokesh
-- Create date: <Create Date,,>
-- Description:	to insert and get task data  
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetTask]
@int_TaskID int=NULL,
@vc_TaskName varchar(100)=Null,
@vc_TaskDesc varchar(300)=Null,
@int_TaskTypeID int=Null,
@int_TaskOwnerID int=Null,
@int_SubjectID int=Null,
@vc_TopicId varchar(50)=Null,
@int_LibraryID int=Null,
@int_IsVerificationReq int=Null,
@int_TestID int=Null,
@dt_StartDate smalldatetime=Null,
@dt_EndDate smalldatetime=Null,
--@vc_GroupId varchar(500)=Null,
@vc_UserId varchar(500)=Null,
@int_Status int=Null,
@vc_Remarks varchar(300)=Null,
@int_TransType int=Null
AS
BEGIN
    --to insert task 
    if(@int_TransType=1)
    BEGIN
	IF (SELECT COUNT(TaskID) FROM tbl_TaskMaster WHERE TaskName=@vc_TaskName) = 0
		BEGIN
	       INSERT INTO tbl_TaskMaster(TaskName,TaskDesc,TaskTypeID,TaskOwnerID,SubjectID,TopicID,LibraryID,IsVerificationRequired,TestID,StartDate,EndDate,status) VALUES (@vc_TaskName,@vc_TaskDesc,@int_TaskTypeID,@int_TaskOwnerID,@int_SubjectID,@vc_TopicId,@int_LibraryID,@int_IsVerificationReq,@int_TestID,@dt_StartDate,@dt_EndDate,@int_Status)
	DECLARE @insertcountId int
	DECLARE @k int
	
	
	--temporary table for having splitted userids
	CREATE TABLE #temp(
	id int primary key identity(1,1),
	MyString UNIQUEIDENTIFIER
	)
	INSERT INTO #temp  SELECT * FROM Mysplitfunction(@vc_UserId,'|') 
    
	
	SET @insertcountId=(SELECT COUNT(id) from #temp)
	
	set @k=1
	while(@k<=@insertcountId)
	BEGIN
	DECLARE @userid UNIQUEIDENTIFIER
	SET @userid=(select #temp.MyString from #temp where #temp.id=@k)
	BEGIN		
		DECLARE @taskid int
		SET @taskid=(select TaskID from tbl_TaskMaster where TaskName=@vc_TaskName)
		INSERT INTO tbl_TaskTransactions(TaskID,UserID,Status,Remarks)values(@taskid,@userid,@int_Status,@vc_Remarks)
		
	    SELECT 'task transacted successfully'
	END
   SET @k=@k+1
END
END
END	
 --to modify tasks
    if(@int_TransType=2)

   BEGIN
	   UPDATE tbl_TaskMaster SET TaskName=@vc_TaskName,TaskDesc=@vc_TaskDesc,TaskTypeID=@int_TaskTypeID,TaskOwnerID=@int_TaskOwnerID,SubjectID=@int_SubjectID,TopicID=@vc_TopicId,LibraryID=@int_LibraryID,IsVerificationRequired=@int_IsVerificationReq,TestID=@int_TestID,StartDate=@dt_StartDate,EndDate=@dt_EndDate,status=@int_Status WHERE TaskID=@int_TaskID
	   DELETE FROM tbl_TaskTransactions WHERE TaskID=@int_TaskID
	
	DECLARE @countId int
	DECLARE @j int 
	CREATE TABLE #updatetasktemp(
	id int primary key identity(1,1),
	MyString UNIQUEIDENTIFIER
	)
	INSERT INTO #updatetasktemp  SELECT * FROM Mysplitfunction(@vc_UserId,'|') 
	SET @countId=(SELECT COUNT(id) from #updatetasktemp)
	
	set @j=1
	while(@j<=@countId)
	BEGIN
	DECLARE @updateuserid UNIQUEIDENTIFIER
	SET @updateuserid=(select #updatetasktemp.MyString from #updatetasktemp where #updatetasktemp.id=@j)
	BEGIN		
		DECLARE @updatetaskid int
		SET @updatetaskid=(select TaskID from tbl_TaskMaster where TaskName=@vc_TaskName)   
    
		 INSERT INTO tbl_TaskTransactions(TaskID,UserID,Status,Remarks)values(@updatetaskid,@updateuserid,@int_Status,@vc_Remarks)
   
    END
     SET @j=@j+1
   END
 END
  
 --to retrieve tasks inorder to display in manage task grid
  if(@int_TransType=3)
  
    BEGIN
    DECLARE @topicname varchar(500)
    DECLARE @l int
    DECLARE @id int
    CREATE TABLE #tempid(
	id int primary key identity(1,1),
	MyString varchar(1000),
	taskid int
	
	)
	CREATE TABLE #temporaryid(
	id int primary key identity(1,1),
	MyString varchar(1000)
	)
	CREATE TABLE #countid(
	
	value int
	)
	
	CREATE TABLE #temptask(
	TaskID int,TaskName varchar(50),TaskDesc varchar(100),TaskType varchar(50),vc_FirstName varchar(50),TestName varchar(50)
	,SubjectName varchar(50),topicname varchar(500),IsVerificationRequired int,LibraryID int,StartDate smalldatetime,EndDate smalldatetime ,status int
	
	)
	
    INSERT INTO #tempid  SELECT TopicID,TaskID FROM tbl_TaskMaster
   
	
	SET @id=(SELECT COUNT(id) from #tempid)
	DECLARE @val int=NUll
	SET @val=(SELECT COUNT(id) from #tempid WHERE #tempid.MyString is not null )
	INSERT INTO #countid select @val
	SELECT * FROM #countid 
	
	set @l=1
	while(@l<=@id)
	BEGIN
	
	DECLARE @tasktype varchar(50)
    SET @tasktype =(SELECT TTM.TaskName FROM #tempid,tbl_TaskMaster TM inner join tbl_TaskTypeMaster TTM ON TTM.TaskTypeId=TM.TaskTypeID WHERE #tempid.id=@l AND #tempid.taskid=TM.TaskID )
    
    if(@tasktype='Assignment')
    
    BEGIN
       DECLARE @topicid varchar(1000)
       
	   SET @topicid=(select #tempid.MyString  from #tempid where #tempid.id=@l)
       
       EXEC sp_GetTopicListsById @topicid,@topicname OUT
       
      BEGIN
      INSERT INTO #temptask 
      SELECT  TM.TaskID,TM.TaskName,TM.TaskDesc,TTM.TaskName as TaskType,UM.UserName,TLM.TestName,SM.SubjectName,@topicname,TM.IsVerificationRequired,TM.LibraryID,TM.StartDate,TM.EndDate,TM.status 
      FROM
      #tempid,
      tbl_TaskMaster TM inner join tbl_TaskTypeMaster TTM ON TTM.TaskTypeId=TM.TaskTypeID
      
	  inner join aspnet_Users UM ON UM.UserID=TM.TaskOwnerID
      inner join tbl_TestListMaster TLM ON TLM.TestId=TM.TestID
      inner join tbl_SubjectMaster SM ON SM.SubjectId=TM.SubjectID
       WHERE #tempid.id=@l and #tempid.taskid=TM.TaskID
      END
     END
      else
      BEGIN
      INSERT INTO #temptask 
      SELECT  TM.TaskID,TM.TaskName,TM.TaskDesc,TTM.TaskName as TaskType,UM.UserName,TLM.TestName,'Null' as SubjectName,'Null' as topicname,TM.IsVerificationRequired,TM.LibraryID,TM.StartDate,TM.EndDate,TM.status 
      FROM
      #tempid,
      tbl_TaskMaster TM inner join tbl_TaskTypeMaster TTM ON TTM.TaskTypeId=TM.TaskTypeID
      
	  inner join aspnet_Users UM ON UM.UserID=TM.TaskOwnerID
      inner join tbl_TestListMaster TLM ON TLM.TestId=TM.TestID
      WHERE #tempid.id=@l and #tempid.taskid=TM.TaskID
      END
       
         SET @l=@l+1
     
      
      --END
     
      
    END
      SELECT TT.TaskID,TT.TaskName,TT.TaskDesc,TT.TaskType,TT.vc_FirstName,TT.TestName,TT.SubjectName,TT.topicname,TT.IsVerificationRequired,TT.LibraryID,TT.StartDate,TT.EndDate,TT.status  
      FROM #temptask TT
    END
    --to retrieve tasks inorder to display in create task page for editing  
    if(@int_TransType=4)
    BEGIN
    
      SELECT TM.TaskName,TM.TaskDesc,TM.TaskTypeID,TM.TaskOwnerID,TM.TestID,TM.SubjectID,TM.TopicID,TM.StartDate,TM.EndDate,TM.status FROM tbl_TaskMaster TM WHERE TM.TaskID=@int_TaskID 
	END
	--to retrieve users for  editing  task
	if(@int_TransType=5)
	BEGIN
	   SELECT UM.UserID,UM.UserName FROM aspnet_Users UM inner join tbl_TaskTransactions TT ON UM.UserID=TT.UserID
	   WHERE TaskID=@int_TaskID
	END
	
END
--END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetSetTaskTypeMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		kavitha
-- Create date: <Create Date,,>
-- Description:	to set and modify task
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetTaskTypeMaster] 
	@vc_TaskName varchar(50)=Null,
	@vc_rbtnFollowUp varchar(50)=Null,
	@vc_FollowUpType varchar(50)=Null,
	@int_TypeId int=Null,
	@int_TransType int=Null
	
AS
BEGIN
if(@int_TransType=1)
BEGIN
  IF (SELECT COUNT(TaskName) FROM tbl_TaskTypeMaster WHERE TaskName=@vc_TaskName) = 0
     BEGIN
		INSERT INTO tbl_TaskTypeMaster(TaskName,FollowUp,FollowUpType) VALUES(@vc_TaskName,@vc_rbtnFollowUp,@vc_FollowUpType) 
		SELECT 'inserted successfully'
	  END
  ELSE
    BEGIN
       SELECT 'already exists'
    END	
END      
  if(@int_TransType=2)
	  BEGIN
		 UPDATE tbl_TaskTypeMaster SET TaskName=@vc_TaskName,FollowUp=@vc_rbtnFollowUp,FollowUpType=@vc_FollowUpType WHERE TaskTypeId=@int_TypeId
	  END 
  if(@int_TransType=3)
		BEGIN
		   SELECT * FROM tbl_TaskTypeMaster
		END
  if(@int_TransType=4)
		BEGIN
		   SELECT * FROM tbl_TaskTypeMaster WHERE TaskTypeId=@int_TypeId
		END	   	
END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetSetTest]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ankur
-- Create date: 10/3/2011
-- Description:	to insert update and select testInfo
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetTest]
@testName varchar(50)=null,
@subjectid int=null,
@topicId VARCHAR(50)=null,
@isQuestionManual int=null,
@noOfQuestion int=null,
@noOfHighQuestion int=null,
@noOfMediumQuestion int=null,
@noOfLowQuestion int=null,
@cutOffMarks int=null,
@testCreatedDate dateTime=null,
@startDate dateTime=null,
@endDate dateTime=null,
@duration int=null,
@question varchar(50)=null,
@sendCumulativeReport int=null,
@noOfAttempts int=null,
@countOfAttempts int=null,
@refLibrary varchar(50)=null,
@status varchar(50)=null,
@userId VARCHAR(5000)=NULL,
@testNo int=null,
@notify int=null,
@TransType int
AS
BEGIN
if(@TransType=1)--FOR INSERTING NEW TEST INFORMATION.
begin
declare @count int
declare @i int
declare @testId int
insert into tbl_TestListMaster(TestName,SubjectId,IsQuestionManualSelection,NoOfQuestion,NoOfHighQuestion,NoOfMediumQuestion,NoOfLowQuestion,CutOffMarks,TestCreatedDate,StartDate,EndDate,Duration,sendCumulativeReport,NoOfAttempts,CountofAttempts,RefLibrary,states,NotifyTestTaker)
values (@testName,@subjectid,@isQuestionManual,@noOfQuestion,@noOfHighQuestion,@noOfMediumQuestion,@noOfLowQuestion,@cutOffMarks,@testCreatedDate,@startDate,@endDate,@duration,@sendCumulativeReport,@noOfAttempts,@countOfAttempts,@refLibrary,@status,@notify)

set @testId=(select TLM.TestId from tbl_TestListMaster TLM where TLM.TestName=@testName)

create table #tmp(
id int primary key identity(1,1),
MyString varchar(50)
)

insert into #tmp
select *from Mysplitfunction(@question,'|')

set @count=(select COUNT(id) from #tmp)
set @i=1
while(@i<=@count)
begin
declare @questionId varchar(50)
set @questionId=(select #tmp.MyString from #tmp where #tmp.id=@i)
insert into tbl_TestQuestionMaster(TestId,QuestionId) values(@testId,@questionId)
set @i=@i+1
end

create table #temp(
id int primary key identity(1,1),
MyString varchar(50)
)

DECLARE @countI INT
DECLARE @J INT

insert into #temp
select *from Mysplitfunction(@topicId,'|')

set @countI=(select COUNT(id) from #temp)
set @J=1
while(@J<=@countI)
begin
declare @topId varchar(50)
set @topId=(select #temp.MyString from #temp where #temp.id=@J)
insert into tbl_TestTopicMapping(TestId,TopicId) values(@testId,@topId)
set @J=@J+1
end

create table #temp1(
id int primary key identity(1,1),
MyString uniqueidentifier
)

DECLARE @countId INT
DECLARE @k INT

insert into #temp1
select *from Mysplitfunction(@userId,'|')

set @countId=(select COUNT(id) from #temp1)
set @k=1
while(@k<=@countId)
begin
declare @useId uniqueidentifier
set @useId=(select #temp1.MyString from #temp1 where #temp1.id=@k)
insert into tbl_TestUserMapping(TestId,UserId,states) values(@testId,@useId,@status)
set @k=@k+1
end

end

ELSE IF(@TransType=2)--for selection
BEGIN
SELECT TLM.TestId AS 'TestId',TLM.TestName AS 'TestName',SM.SubjectName AS 'SubjectName',TLM.IsQuestionManualSelection,TLM.NoOfQuestion,
TLM.NoOfHighQuestion,TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.states
FROM tbl_TestListMaster TLM inner join tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId

SELECT TLM.TestId,TM.topicName FROM tbl_TestListMaster TLM INNER JOIN tbl_TestTopicMapping TTM
ON TTM.TestId=TLM.TestId INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId 
END

ELSE IF(@TransType=3)--for SELECTING TEST BASED ON SubID AND TopicId
BEGIN

IF((@subjectid=0)AND (@topicId=0))--ALL SUBJECTS AND ALL TOPICS
BEGIN
SELECT TLM.TestId AS 'TestId',TLM.TestName AS 'TestName',SM.SubjectName AS 'SubjectName',TLM.IsQuestionManualSelection,TLM.NoOfQuestion,
TLM.NoOfHighQuestion,TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.states,TM.topicName AS 'Topics'
FROM tbl_TestListMaster TLM inner join tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId
INNER JOIN tbl_TestTopicMapping TTM ON TLM.TestId=TTM.TestId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId order by TLM.TestName
END

ELSE IF((@subjectid!=0) AND(@topicId!=0))--FOR PARTICULAR SUBJECT PARTICULAR TOPIC
BEGIN
SELECT TLM.TestId AS 'TestId',TLM.TestName AS 'TestName',SM.SubjectName AS 'SubjectName',TLM.IsQuestionManualSelection,TLM.NoOfQuestion,
TLM.NoOfHighQuestion,TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.states,TM.topicName AS 'Topics'
FROM tbl_TestListMaster TLM inner join tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId
INNER JOIN tbl_TestTopicMapping TTM ON TLM.TestId=TTM.TestId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TLM.SubjectId=@subjectid AND TTM.TopicId=@topicId order by TLM.TestName
END

ELSE IF((@subjectid=0) AND (@topicId!=0))--FOR ALL SUBJECTS BUT PARTICULAR TOPIC.
BEGIN
SELECT TLM.TestId AS 'TestId',TLM.TestName AS 'TestName',SM.SubjectName AS 'SubjectName',TLM.IsQuestionManualSelection,TLM.NoOfQuestion,
TLM.NoOfHighQuestion,TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.states,TM.topicName AS 'Topics'
FROM tbl_TestListMaster TLM inner join tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId
INNER JOIN tbl_TestTopicMapping TTM ON TLM.TestId=TTM.TestId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE  TTM.TopicId=@topicId order by TLM.TestName
END

ELSE IF((@subjectid!=0) AND (@topicId=0))--FOR PURTICULAR SUBJECT BUT ALL TOPICS
BEGIN
SELECT TLM.TestId AS 'TestId',TLM.TestName AS 'TestName',SM.SubjectName AS 'SubjectName',TLM.IsQuestionManualSelection,TLM.NoOfQuestion,
TLM.NoOfHighQuestion,TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.states,TM.topicName AS 'Topics'
FROM tbl_TestListMaster TLM inner join tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId
INNER JOIN tbl_TestTopicMapping TTM ON TLM.TestId=TTM.TestId
INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TLM.SubjectId=@subjectid order by TLM.TestName
END

END

ELSE IF(@TransType=4)--FOR SELECTING TEST INFORMATION BASED ON TESTID
BEGIN
SELECT TLM.* FROM tbl_TestListMaster TLM WHERE TLM.TestId=@testNo
END

ELSE IF(@TransType=5)--FOR SELECTING TOPIC INFORMATION BASED ON TESTID
BEGIN
SELECT TM.topicName,TM.topicId FROM tbl_TestTopicMapping TTM INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TTM.TestId=@testNo
END

ELSE IF(@TransType=6)--FOR SELECTING QUESTION INFORMATION BASED ON TESTID
BEGIN
SELECT TQM.QuestionId FROM tbl_TestQuestionMaster TQM
WHERE TQM.TestId=@testNo
END

ELSE IF(@TransType=7)--FOR UPDATING TEST INFORMATION BASED ON TESTID
BEGIN
UPDATE tbl_TestListMaster
SET TestName=@testName,SubjectId=@subjectid,IsQuestionManualSelection=@isQuestionManual,NoOfQuestion=@noOfQuestion,
NoOfHighQuestion=@noOfHighQuestion,NoOfMediumQuestion=@noOfMediumQuestion,NoOfLowQuestion=@noOfLowQuestion,CutOffMarks=@cutOffMarks,
StartDate=@startDate,EndDate=@endDate,Duration=@duration,sendCumulativeReport=@sendCumulativeReport,NoOfAttempts=@noOfAttempts,CountofAttempts=@countOfAttempts,
RefLibrary=@refLibrary,States=@status,NotifyTestTaker=@notify
WHERE TestId=@testNo

DELETE  FROM tbl_TestTopicMapping WHERE TestId=@testNo
DELETE  FROM tbl_TestUserMapping WHERE TestId=@testNo
DELETE FROM tbl_TestQuestionMaster WHERE TestId=@testNo

create table #tmp1(
id int primary key identity(1,1),
MyString varchar(50)
)

declare @count1 int
declare @i1 int

insert into #tmp1
select *from Mysplitfunction(@question,'|')

set @count1=(select COUNT(id) from #tmp1)
set @i1=1
while(@i1<=@count1)
begin
declare @questionI varchar(50)
set @questionI=(select #tmp1.MyString from #tmp1 where #tmp1.id=@i1)
insert into tbl_TestQuestionMaster(TestId,QuestionId) values(@testNo,@questionI)
set @i1=@i1+1
end

create table #temp11(
id int primary key identity(1,1),
MyString varchar(50)
)

DECLARE @countI1 INT
DECLARE @J1 INT

insert into #temp11
select *from Mysplitfunction(@topicId,'|')

set @countI1=(select COUNT(id) from #temp11)
set @J1=1
while(@J1<=@countI1)
begin
declare @topId1 varchar(50)
set @topId1=(select #temp11.MyString from #temp11 where #temp11.id=@J1)
insert into tbl_TestTopicMapping(TestId,TopicId) values(@testNo,@topId1)
set @J1=@J1+1
end

create table #temp21(
id int primary key identity(1,1),
MyString uniqueidentifier
)

DECLARE @countId1 INT
DECLARE @k1 INT

insert into #temp21
select *from Mysplitfunction(@userId,'|')

set @countId1=(select COUNT(id) from #temp21)
set @k1=1
while(@k1<=@countId1)
begin
declare @useId1 uniqueidentifier
set @useId1=(select #temp21.MyString from #temp21 where #temp21.id=@k1)
insert into tbl_TestUserMapping(TestId,UserId,states) values(@testNo,@useId1,@status)
set @k1=@k1+1
end

END

ELSE IF(@TransType=8)--FOR SELECTING TEST STATUS INFORMATION BASED ON TESTID
BEGIN
SELECT TLM.States FROM tbl_TestListMaster TLM WHERE TLM.TestId=@testNo
END

ELSE IF(@TransType=9)--FOR SELECTING TEST USER INFORMATION BASED ON TESTID
BEGIN
SELECT distinct(TUM.UserId),UM.UserName
FROM tbl_TestUserMapping TUM INNER JOIN aspnet_Users UM ON UM.UserID=TUM.UserId
WHERE TUM.TestId=@testNo

END


ELSE IF(@TransType=10)--FOR UPDATING TEST STATUS INFORMATION
BEGIN
UPDATE tbl_TestUserMapping 
SET States=@status
WHERE TestId=@testNo AND UserId=@userId

DECLARE @CountTotalUsersAssigned INT
DECLARE @CountTotalUsersCompleted INT


SET @CountTotalUsersAssigned=(SELECT COUNT(TestId) FROM tbl_TestUserMapping WHERE TestId=@testNo)
SET @CountTotalUsersCompleted=(SELECT COUNT(TestId) FROM tbl_TestUserMapping WHERE States='Completed')

IF(@CountTotalUsersAssigned=@CountTotalUsersCompleted)
BEGIN
UPDATE tbl_TestListMaster
SET States='Completed'
WHERE TestId=@testNo
END

ELSE IF (@CountTotalUsersAssigned>@CountTotalUsersCompleted)
BEGIN
UPDATE tbl_TestListMaster
SET States='InProgress'
WHERE TestId=@testNo
END

END


END


GO
/****** Object:  StoredProcedure [dbo].[SP_GetSetTestAttempt]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		ANKUR BHATNAGAR
-- Create date: 18/3/2011
-- Description:	INFORMATION ABOUT TEST ATTEMPT.
-- =============================================
CREATE PROCEDURE [dbo].[SP_GetSetTestAttempt]
@testId INT=NULL,
@Result VARCHAR(50)=NULL,
@Score INT=NULL,
@AttemptNo int=NULL,
@UserId UNIQUEIDENTIFIER=NULL,
@QuestionId VARCHAR(50)=NULL,
@Answer VARCHAR(50)=NULL,
@TimeTaken INT=NULL,
@Status VARCHAR(50)=NULL,
@TransType INT
AS
BEGIN
CREATE TABLE #TempTable(
ID INT PRIMARY KEY IDENTITY(1,1),
AttID INT
)

CREATE TABLE #TempTable1(
ID INT PRIMARY KEY IDENTITY(1,1),
AttID INT,
QID INT,
OPTIONS VARCHAR(50)
)
IF(@TransType=1)--FOR INSERTING NEW RECORD OF TEST ATTEMPT BY USER.
BEGIN
DECLARE @TestAttId INT
INSERT INTO tbl_TestAttemptMaster(TestId,UserId,Result,Score,AttemptNo,AttemptDate,TimeTaken) VALUES(@testId,@UserId,@Result,@Score,@AttemptNo,GETDATE(),@TimeTaken)
SET @TestAttId=@@IDENTITY

CREATE TABLE #tmp(
Id INT PRIMARY KEY IDENTITY(1,1),
QId INT)

CREATE TABLE #temp(
Id INT PRIMARY KEY IDENTITY(1,1),
OptionId VARCHAR(50))

INSERT INTO #tmp
SELECT * FROM Mysplitfunction(@QuestionId,'|')

INSERT INTO #temp
SELECT * FROM Mysplitfunction(@Answer,'&')

DECLARE @Count INT
DECLARE @I INT

SET @Count=(SELECT COUNT(Id) FROM #temp)
SET @I=1

WHILE(@I<=@Count)
BEGIN
DECLARE @QuesId INT
DECLARE @Ans VARCHAR(50)
SET @QuesId=(SELECT T1.QId FROM #tmp T1 WHERE T1.Id=@I)
SET @Ans=(SELECT T.OptionId FROM #temp T WHERE T.Id=@I)
INSERT INTO tbl_TestAttemptQuestion(TestAttemptId,QuestionId,OptionId) VALUES(@TestAttId,@QuesId,@Ans)
SET @I=@I+1
END
END

IF(@TransType=2)--FOR SELECTING TEST ATTEMPT NO INFORMATION BASED ON USERID AND TESTID
BEGIN
SELECT TAM.AttemptNo FROM tbl_TestAttemptMaster TAM
WHERE TAM.UserId=@UserId AND TAM.TestId=@testId
END

IF(@TransType=3)--FOR SELECTING TEST ATTEMPT  INFORMATION BASED ON USERID AND TESTID
BEGIN
SELECT TAM.TestAttemptId,TAM.AttemptNo,TAM.AttemptDate,TAM.Result,TAM.Score FROM tbl_TestAttemptMaster TAM
WHERE TAM.UserId=@UserId AND TAM.TestId=@testId
END

IF(@TransType=4)--FOR SELECTING TEST MAX ATTEMPT INFORMATION BASED ON TESTID
BEGIN
SELECT TLM.CountofAttempts FROM tbl_TestListMaster TLM 
WHERE TLM.NoOfAttempts=1 AND TLM.TestId=@testId
END

IF(@TransType=5)--FOR SELECTING PREVIOUS  ATTEMPT SCORE CARD INFORMATION BASED ON TESTID AND USERID
BEGIN
INSERT INTO #TempTable
SELECT TAM.TestAttemptId FROM tbl_TestAttemptMaster TAM WHERE TAM.UserId=@UserId AND TAM.TestId=@testId

DECLARE @AttId INT
DECLARE @cnt INT 
DECLARE @var INT

SET @cnt=(SELECT COUNT(ID) FROM #TempTable)
SET @var=1
WHILE(@var<=@cnt)
BEGIN
SET @AttId=(SELECT T.AttID FROM #TempTable T WHERE T.ID=@var)
INSERT INTO #TempTable1
SELECT * FROM tbl_TestAttemptQuestion TAQ WHERE TAQ.TestAttemptId=@AttId
SET @var=@var+1
END
SELECT * FROM #TempTable1
END

IF(@TransType=6)--FOR SELECTING TEST ATTEMPT  INFORMATION BASED ON USERID AND TESTID
BEGIN

IF(@Status='Completed')
BEGIN
SELECT DISTINCT(TAM.TestAttemptId),UM.UserName,TUM.States,TAM.AttemptNo,TAM.AttemptDate,TAM.Result,TAM.Score
FROM tbl_TestAttemptMaster TAM INNER JOIN tbl_TestUserMapping TUM ON TUM.UserId=TAM.UserId INNER JOIN aspnet_Users UM ON UM.UserId=TAM.UserId
WHERE TAM.TestId=@testId AND TUM.States = 'Completed' AND TUM.TestId=@testId AND TAM.Result='Pass'
END

ELSE IF(@Status='Not Completed')
BEGIN
SELECT DISTINCT(TAM.TestAttemptId),UM.UserName,TUM.States,TAM.AttemptNo,TAM.AttemptDate,TAM.Result,TAM.Score
FROM tbl_TestAttemptMaster TAM INNER JOIN tbl_TestUserMapping TUM ON TUM.UserId=TAM.UserId INNER JOIN aspnet_Users UM ON UM.UserId=TAM.UserId
WHERE TAM.TestId=@testId AND TUM.States != 'Completed' AND TUM.TestId=@testId AND TAM.Result='Fail'
END


ELSE IF(@Status='Not Attempted')
BEGIN
SELECT UM.UserName,'NULL' AS 'TestAttemptId','NULL' AS 'AttemptNO' ,'NULL' AS 'Score' 
FROM tbl_TestUserMapping TUM INNER JOIN aspnet_Users UM ON UM.UserId=TUM.UserId
WHERE TUM.UserId NOT IN (SELECT UserId FROM tbl_TestAttemptMaster WHERE TestId = @testId) AND TUM.TestId=@testId
--SELECT UM.UserName
--FROM tbl_TestAttemptMaster TAM INNER JOIN tbl_TestUserMapping TUM ON TUM.UserId!=TAM.UserId AND TUM.TestId=TAM.TestId INNER JOIN aspnet_Users UM ON UM.UserId=TAM.UserId
--WHERE TAM.TestId=@testId AND TUM.States = 'Scheduled' AND TUM.TestId=@testId
END

END

END

--SELECT DISTINCT TAM.TestAttemptId, TAM.TestId, UM.UserName, TUM.States,TAM.AttemptNo,TAM.AttemptDate,TAM.Result,TAM.Score
--FROM tbl_TestAttemptMaster TAM INNER JOIN tbl_TestUserMapping TUM ON TUM.UserId=TAM.UserId INNER JOIN aspnet_Users UM ON UM.UserId=TAM.UserId
--WHERE TAM.TestId=17 AND TUM.States = 'Completed'
GO
/****** Object:  StoredProcedure [dbo].[sp_GetSetUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ANKUR
-- Create date: 25/3/2010
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetUser]
@userId UNIQUEIDENTIFIER
AS
BEGIN
	SELECT UM.UserName FROM aspnet_Users UM WHERE UM.UserID=@userId
END

GO
/****** Object:  StoredProcedure [dbo].[sp_GetTestCompletedList]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:	   ANKUR BHATNAGAR
-- Create date: 22/3/2010
-- Description:	ALL TEST COMPLETED INFORMATION 
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTestCompletedList]
@Userid UNIQUEIDENTIFIER = null,
@Status VARCHAR(50) = null,
@AttemptId INT = null,
@TransType INT
AS
BEGIN
IF(@TransType=1)
BEGIN
--SELECT DISTINCT(TAM.TestAttemptId)AS 'TestId',TLM.TestName,SM.SubjectName,TLM.StartDate,TLM.EndDate,TUM.States,TAM.AttemptNo,TAM.TestAttemptId
--FROM tbl_TestUserMapping TUM INNER JOIN tbl_TestListMaster TLM ON TLM.TestId=TUM.TestId INNER JOIN
--tbl_SubjectMaster SM ON TLM.SubjectId=SM.SubjectId INNER JOIN tbl_TestAttemptMaster TAM ON TAM.TestId=TUM.TestId
--WHERE TUM.UserId=@Userid AND TUM.States=@Status 

SELECT DISTINCT(TAM.TestAttemptId) AS 'TestId',TLM.TestName,SM.SubjectName,TLM.StartDate,TLM.EndDate,TUM.States,TAM.AttemptNo,tam.TestAttemptId
FROM tbl_TestAttemptMaster TAM INNER JOIN tbl_TestListMaster TLM ON TAM.TestId=TLM.TestId INNER JOIN tbl_TestUserMapping TUM ON TUM.TestId=TLM.TestId INNER JOIN tbl_SubjectMaster SM
ON SM.SubjectId=TLM.SubjectId
WHERE TAM.UserId=@Userid AND TUM.UserId=@Userid
END 

ELSE IF(@TransType=2)
BEGIN
SELECT * FROM tbl_TestAttemptMaster TAM WHERE TAM.TestAttemptId=@AttemptId
END


ELSE IF(@TransType=3)
BEGIN
SELECT * FROM tbl_TestAttemptQuestion TAQ WHERE TAQ.TestAttemptId=@AttemptId
END

END


GO
/****** Object:  StoredProcedure [dbo].[SP_GetTestInfoBasedOnTopicAndSubject]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		ANKUR BHATNAGAR
-- Create date: 21-03-2010
-- Description:	RETRIVE TEST INFORMATION BASED ON SUBJECT ID AND TOPIC ID
-- =============================================
CREATE PROCEDURE [dbo].[SP_GetTestInfoBasedOnTopicAndSubject]
@subjectid int,
@topicId int 
AS
BEGIN

CREATE TABLE #Temp(
ID INT PRIMARY KEY IDENTITY(1,1),
TestId INT,
TestName VARCHAR(50),
SubjectName VARCHAR(50),
IsQuestionManualSelection INT,
NoOfQuestion INT,
NoOfHighQuestion INT,
NoOfMediumQuestion INT,
NoOfLowQuestion INT,
CutOffMarks INT,
TestCreatedDate DATETIME,
StartDate DATETIME,
EndDate DATETIME,
NoOfAttempts INT,
States VARCHAR(50)
)

CREATE TABLE #Tmp(
TESTID INT,
TOPICNAME VARCHAR(50)
)

DECLARE @CNT INT
DECLARE @VAR INT
DECLARE @TSTID INT


IF((@subjectid!=0) AND(@topicId!=0))--FOR PARTICULAR SUBJECT PARTICULAR TOPIC
BEGIN
INSERT INTO #Temp
SELECT TLM.TestId,TLM.TestName,SM.SubjectName,TLM.IsQuestionManualSelection,TLM.NoOfQuestion,TLM.NoOfHighQuestion,
TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.States
FROM tbl_TestListMaster TLM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId
WHERE TLM.SubjectId=@subjectid

SET @CNT=(SELECT COUNT(ID)FROM #Temp)
SET @VAR=1

WHILE(@VAR<=@CNT)
BEGIN

SET @TSTID=(SELECT T.TestId FROM #Temp T WHERE T.ID=@VAR)
INSERT INTO #Tmp
SELECT TTM.TestId,TM.topicName FROM tbl_TestTopicMapping TTM INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TTM.TestId=@TSTID AND TTM.TopicId=@topicId
SET @VAR=@VAR+1
END

SELECT DISTINCT(T.TestId),T.TestName,T.SubjectName,T.IsQuestionManualSelection,T.NoOfQuestion,T.NoOfHighQuestion,
T.NoOfMediumQuestion,T.NoOfLowQuestion,T.CutOffMarks,T.TestCreatedDate,T.StartDate,T.EndDate,T.NoOfAttempts,T.States
FROM #Temp T INNER JOIN #Tmp T1 ON T.TestId=T1.TESTID ORDER BY T.TestName
SELECT * FROM #Tmp


END

ELSE IF((@subjectid=0) AND (@topicId!=0))--FOR ALL SUBJECTS BUT PARTICULAR TOPIC.
BEGIN
INSERT INTO #Temp
SELECT TLM.TestId,TLM.TestName,SM.SubjectName,TLM.IsQuestionManualSelection,TLM.NoOfQuestion,TLM.NoOfHighQuestion,
TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.States
FROM tbl_TestListMaster TLM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId


SET @CNT=(SELECT COUNT(ID)FROM #Temp)
SET @VAR=1

WHILE(@VAR<=@CNT)
BEGIN

SET @TSTID=(SELECT T.TestId FROM #Temp T WHERE T.ID=@VAR)
INSERT INTO #Tmp
SELECT TTM.TestId,TM.topicName FROM tbl_TestTopicMapping TTM INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TTM.TestId=@TSTID AND TTM.TopicId=@topicId
SET @VAR=@VAR+1
END

SELECT DISTINCT(T.TestId),T.TestName,T.SubjectName,T.IsQuestionManualSelection,T.NoOfQuestion,T.NoOfHighQuestion,
T.NoOfMediumQuestion,T.NoOfLowQuestion,T.CutOffMarks,T.TestCreatedDate,T.StartDate,T.EndDate,T.NoOfAttempts,T.States
FROM #Temp T INNER JOIN #Tmp T1 ON T.TestId=T1.TESTID ORDER BY T.TestName
SELECT * FROM #Tmp


END

ELSE IF((@subjectid!=0) AND (@topicId=0))--FOR PURTICULAR SUBJECT BUT ALL TOPICS
BEGIN
INSERT INTO #Temp
SELECT TLM.TestId,TLM.TestName,SM.SubjectName,TLM.IsQuestionManualSelection,TLM.NoOfQuestion,TLM.NoOfHighQuestion,
TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.States
FROM tbl_TestListMaster TLM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId
WHERE TLM.SubjectId=@subjectid

SET @CNT=(SELECT COUNT(ID)FROM #Temp)
SET @VAR=1

WHILE(@VAR<=@CNT)
BEGIN

SET @TSTID=(SELECT T.TestId FROM #Temp T WHERE T.ID=@VAR)
INSERT INTO #Tmp
SELECT TTM.TestId,TM.topicName FROM tbl_TestTopicMapping TTM INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TTM.TestId=@TSTID
SET @VAR=@VAR+1
END

SELECT DISTINCT(T.TestId),T.TestName,T.SubjectName,T.IsQuestionManualSelection,T.NoOfQuestion,T.NoOfHighQuestion,
T.NoOfMediumQuestion,T.NoOfLowQuestion,T.CutOffMarks,T.TestCreatedDate,T.StartDate,T.EndDate,T.NoOfAttempts,T.States
FROM #Temp T INNER JOIN #Tmp T1 ON T.TestId=T1.TESTID ORDER BY T.TestName
SELECT * FROM #Tmp


END

ELSE IF((@subjectid=0)AND (@topicId=0))--ALL SUBJECTS AND ALL TOPICS
BEGIN
INSERT INTO #Temp
SELECT TLM.TestId,TLM.TestName,SM.SubjectName,TLM.IsQuestionManualSelection,TLM.NoOfQuestion,TLM.NoOfHighQuestion,
TLM.NoOfMediumQuestion,TLM.NoOfLowQuestion,TLM.CutOffMarks,TLM.TestCreatedDate,TLM.StartDate,TLM.EndDate,TLM.NoOfAttempts,TLM.States
FROM tbl_TestListMaster TLM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId


SET @CNT=(SELECT COUNT(ID)FROM #Temp)
SET @VAR=1

WHILE(@VAR<=@CNT)
BEGIN

SET @TSTID=(SELECT T.TestId FROM #Temp T WHERE T.ID=@VAR)
INSERT INTO #Tmp
SELECT TTM.TestId,TM.topicName FROM tbl_TestTopicMapping TTM INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TTM.TestId=@TSTID
SET @VAR=@VAR+1
END

SELECT DISTINCT(T.TestId),T.TestName,T.SubjectName,T.IsQuestionManualSelection,T.NoOfQuestion,T.NoOfHighQuestion,
T.NoOfMediumQuestion,T.NoOfLowQuestion,T.CutOffMarks,T.TestCreatedDate,T.StartDate,T.EndDate,T.NoOfAttempts,T.States
FROM #Temp T INNER JOIN #Tmp T1 ON T.TestId=T1.TESTID ORDER BY T.TestName
SELECT * FROM #Tmp


END

END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetTestInfoByTestId]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		ANKUR BHATNAGAR
-- Create date: 7/12/2010
-- Description:	To get TestInfo By TestId
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTestInfoByTestId]
@TestId int
AS
BEGIN
CREATE TABLE #Tmp
(
ID INT PRIMARY KEY IDENTITY(1,1),
TestId INT,
)

CREATE TABLE #Temp
(
TestId INT,
TopicName VARCHAR(50),
)
DECLARE @Count INT
DECLARE @I INT
 
 SELECT DISTINCT(TLM.TestId),TLM.TestName,SM.SubjectName,TLM.NoOfQuestion,TLM.CutOffMarks,TLM.Duration,TLM.EndDate,TLM.RefLibrary
 FROM tbl_TestListMaster TLM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId
 WHERE  TLM.TestId = @TestId
 
 INSERT INTO #Tmp
 SELECT DISTINCT(TLM.TestId)
 FROM tbl_TestListMaster TLM INNER JOIN tbl_SubjectMaster SM ON SM.SubjectId=TLM.SubjectId
 WHERE  TLM.TestId = @TestId
 
SET @I=1
SET @Count=(SELECT COUNT(ID)FROM #Tmp)


WHILE(@I<=@Count)
BEGIN
DECLARE @test INT
SET @test=(SELECT TestId FROM #Tmp T WHERE T.ID=@I)
INSERT INTO #Temp
SELECT TTM.TestId,TM.topicName FROM tbl_TestTopicMapping TTM INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TTM.TestId=@TEST
SET @I=@I+1
END

SELECT * FROM #Temp


SELECT distinct(TUM.UserId),UM.UserName,TUM.States
FROM tbl_TestUserMapping TUM INNER JOIN aspnet_Users UM ON UM.UserID=TUM.UserId
WHERE TUM.TestId=@TestId

 END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetTestInProgressFields]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<JANSI,VIJAYA>
-- Create date: <9-3-2011>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTestInProgressFields]
@testid	INT = null	
AS
BEGIN
 
SELECT TestId, TestName, NoOfQuestion, Duration,CutOffMarks FROM tbl_TestListMaster WHERE TestId = @testid
   
END 

GO
/****** Object:  StoredProcedure [dbo].[sp_GetTestList]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		ANKUR BHATNAGAR
-- Create date: 19-3-2011
-- Description:	TEST SCHEDULEED,PENDING AND COMPLETED INFORMATION PERTANING TO A PARTICULAR USER
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTestList]
@Userid UNIQUEIDENTIFIER = null,
@Status VARCHAR(50) = null
AS
BEGIN
CREATE TABLE #Tmp
(
ID INT PRIMARY KEY IDENTITY(1,1),
TestId INT,
)

CREATE TABLE #Temp
(
TestId INT,
TopicName VARCHAR(50),
)
DECLARE @Count INT
DECLARE @I INT

SELECT DISTINCT(TUM.TestId),TLM.TestName,SM.SubjectName,TLM.StartDate,TLM.EndDate,TUM.States
FROM tbl_TestUserMapping TUM INNER JOIN tbl_TestListMaster TLM ON TLM.TestId=TUM.TestId INNER JOIN
tbl_SubjectMaster SM ON TLM.SubjectId=SM.SubjectId
WHERE TUM.UserId=@Userid AND TUM.States=@Status  

INSERT INTO #Tmp
SELECT DISTINCT(TLM.TestId)
FROM tbl_TestUserMapping TUM INNER JOIN tbl_TestListMaster TLM ON TLM.TestId=TUM.TestId INNER JOIN
tbl_SubjectMaster SM ON TLM.SubjectId=SM.SubjectId
WHERE TUM.UserId=@Userid AND TUM.States=@Status  


SET @I=1
SET @Count=(SELECT COUNT(ID)FROM #Tmp)


WHILE(@I<=@Count)
BEGIN
DECLARE @test INT
SET @test=(SELECT TestId FROM #Tmp T WHERE T.ID=@I)
INSERT INTO #Temp
SELECT TTM.TestId,TM.topicName FROM tbl_TestTopicMapping TTM INNER JOIN tbl_TopicMaster TM ON TM.topicId=TTM.TopicId
WHERE TTM.TestId=@TEST
SET @I=@I+1
END
SELECT * FROM #Temp

END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetTestName]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		yogi
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTestName]
AS
BEGIN
	select TestId, TestName from tbl_TestListMaster 
END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetTestNames]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ankur
-- Create date: 7-3-2011
-- Description:	returns all test name
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTestNames]
AS
BEGIN
	select TLM.TestName from tbl_TestListMaster TLM
END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetTopicListsById]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<SWATHI,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTopicListsById] 
	 
@TopicId varchar(300),
@topicName varchar(1000)=null output
AS
BEGIN
   --DECLARE @MyTableVar TABLE(TopicId VARCHAR(10))
   -- Call the UDF. Note the use of the explicit default value.
   DECLARE @Count int
   DECLARE @i int
   CREATE TABLE #temptable
   (
      ID int Primary Key Identity(1,1),
      TopicID varchar(300)
   )
   
   CREATE TABLE #temp
   (
      ID int Primary Key Identity(1,1),
      TopicID varchar(300)
   )
   INSERT #temptable SELECT items FROM dbo.Mysplitfunction(@TopicId,'|')
    
    
   --SELECT tm.topicName as TopicNames from #temptable t inner join tbl_TopicMaster tm on t.TopicID = tm.topicId 
   --declare @string varchar(50)=''
   CREATE TABLE #tmp(
      Id int primary key identity(1,1),
      MyString varchar(300)
      )
    Declare  @string varchar(1000)=' '
	SET @Count=(SELECT COUNT(TopicId) FROM #temptable)
	SET @i=1
        WHILE(@i<=@Count)
        BEGIN
           DECLARE @topic varchar(1000)=''
           SET @topic=(SELECT tm.topicName  from #temptable t inner join tbl_TopicMaster tm on t.TopicID = tm.topicId where @i=t.ID )
           set @string += @topic+','
          SET @i=@i+1
       END
       insert into #tmp(MyString) values (@string)
    
    set @topicName= @string
	insert into #temp(TopicID) values (@topicName)
	
	select * from #temp
END 

    



GO
/****** Object:  StoredProcedure [dbo].[sp_GetTopicName]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ankur
-- Create date: 17-3-2011
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTopicName]
@topicId int=NULL,
@TransType INT
as
begin
IF(@TransType=1)
BEGIN
select TM.topicName from tbl_TopicMaster TM where TM.topicId=@topicId
END

ELSE IF(@TransType=2)
BEGIN
SELECT TM.topicId,TM.topicName FROM tbl_TopicMaster TM 
END
END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetTopicRelatedToSubject]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ankur
-- Create date: 3/3/2011
-- Description:	to retrive topic information
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTopicRelatedToSubject]
@subId int
AS
BEGIN
IF(@subId=0)
BEGIN
SELECT TM.topicName,TM.topicId FROM tbl_TopicMaster TM
END
ELSE
BEGIN
SELECT TM.topicName,TM.topicId
FROM tbl_TopicMaster TM WHERE TM.subjectId=@subId
END
END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetTopicRelatedtoTask]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:	kavitha
-- Create date: <Create Date,,>
-- Description:	to get topics selected related to task
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetTopicRelatedtoTask]
	
	@vc_TopicId varchar(100)
AS
BEGIN
   DECLARE @countId int
	DECLARE @j int
	DECLARE @k int
	CREATE TABLE #tasktemp(
	id int primary key identity(1,1),
	MyString varchar(1000)
	)
	INSERT INTO #tasktemp  SELECT * FROM Mysplitfunction(@vc_TopicId,'|')
	
	CREATE TABLE #topictemp(
	id int,
	MyString varchar(1000)
	)
	
	SET @countId=(SELECT COUNT(id) from #tasktemp)
	
	set @k=1
	while(@k<=@countId)
	BEGIN
	DECLARE @topicid int
	SET @topicid=(select cast(#tasktemp.MyString as int) from #tasktemp where #tasktemp.id=@k)
	BEGIN
	  INSERT INTO #topictemp  
	  
	  SELECT topicid,topicName FROM #tasktemp TT   inner join tbl_TopicMaster TM ON TT.MyString=TM.topicId
	  WHERE TM.topicId=@topicid
	 
	END
	SET @k=@k+1
	  
	END
	SELECT * FROM #topictemp
	
END


GO
/****** Object:  StoredProcedure [dbo].[sp_GetUsersandGroups]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		kavitha
-- Create date: <Create Date,,>
-- Description:	to get the users based on Group ID
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetUsersandGroups]
	@groupid int=Null,
	@userid varchar(50)=Null
AS
BEGIN
	if(@groupid= 0 AND @userid='0')
	BEGIN
		SELECT UM.UserId,UM.UserName FROM aspnet_Users UM INNER JOIN aspnet_Membership UME ON UM.UserId=UME.UserId WHERE UME.IsApproved='TRUE' ORDER BY UM.UserName
		--SELECT int_UserID,vc_FirstName,vc_LastName FROM tbl_UserMaster WHERE int_IsActive=1 ORDER BY vc_FirstName
	END
	if(@groupid!=0 AND @userid='0')
	BEGIN
		select UM.UserId,UM.UserName from aspnet_Users UM inner join tbl_UserGroups UG on UG.UserID=UM.UserId INNER JOIN aspnet_Membership UME ON UM.UserId=UME.UserId WHERE UG.GroupID =@groupid AND UME.IsApproved='TRUE'
	     ORDER BY UM.UserName
	    --select UM.int_UserID,vc_FirstName ,vc_LastName from tbl_UserMaster UM inner join tbl_UserGroups UG on UG.int_UserID=UM.int_UserID WHERE UG.int_GroupID =@groupid AND int_IsActive=1
	    -- ORDER BY vc_FirstName
	END
END

--EXEC sp_GetUsersandGroups 34,0

GO
/****** Object:  StoredProcedure [dbo].[sp_ManngeQuestion]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Ankur
-- Create date: 2/03/2011
-- Description:	used to mannage Question-Insert,select and update
-- =============================================
CREATE PROCEDURE [dbo].[sp_ManngeQuestion]
@subId int=null,
@topicId int=null,
@Question varchar(5000)=null,
@option varchar(5000)=null,
@optionId varchar(500)=null,
@answer varchar(500)=null,
@justification varchar(50)=null,
@IsMutiple int=null,
@IsActive int=null,
@priority varchar(50)=null,
@Qid int=null,
@TransType int
AS
BEGIN
if(@TransType=1)--for Insertion
begin
declare @QuestionId int
declare @count int
declare @i int

insert into tbl_QuestionMaster(SubjectId,TopicId,Question,Answer,Justification,priority,IsMultiple,IsActive) values(@subId,@topicId,@Question,@answer,@justification,@priority,@IsMutiple,@IsActive)
set @QuestionId=(select QM.QuestionId from tbl_QuestionMaster QM where QM.SubjectId=@subId and QM.TopicId=@topicId and QM.Question=@Question and QM.Answer=@answer and QM.Justification=@justification and QM.priority=@priority)

create table #tmp(
id int primary key identity(1,1),
MyString varchar(5000)
)

insert into #tmp
select *from Mysplitfunction(@option,'|')

set @count=(select COUNT(id) from #tmp)
set @i=1
while(@i<=@count)
begin
declare @opt varchar(5000)
set @opt=(select #tmp.MyString from #tmp where #tmp.id=@i)
insert into tbl_QuestionOptionMaster values(@opt,@QuestionId)
set @i=@i+1
end
end

else if(@TransType=2)--for selection
begin
IF((@subId=0) AND (@topicId=0))--FOR ALL TOPICS AND ALL SUBJECTS.
BEGIN
select QM.QuestionId,SM.SubjectName,TM.topicName,QM.Question,QM.Answer,QM.Justification,QM.priority,QM.IsMultiple,QM.IsActive
from tbl_QuestionMaster QM inner join tbl_SubjectMaster SM on SM.SubjectId=QM.SubjectId inner join
tbl_TopicMaster TM on TM.topicId=QM.TopicId
END

ELSE IF((@subId!=0) AND (@topicId!=0))-- FOR PARTICULAR TOPIC PARTICULAR SUBJECT.
BEGIN
select QM.QuestionId,SM.SubjectName,TM.topicName,QM.Question,QM.Answer,QM.Justification,QM.priority,QM.IsMultiple,QM.IsActive
from tbl_QuestionMaster QM inner join tbl_SubjectMaster SM on SM.SubjectId=QM.SubjectId inner join
tbl_TopicMaster TM on TM.topicId=QM.TopicId
WHERE QM.SubjectId=@subId AND QM.TopicId=@topicId
END

ELSE IF((@subId=0) AND (@topicId !=0))
BEGIN
select QM.QuestionId,SM.SubjectName,TM.topicName,QM.Question,QM.Answer,QM.Justification,QM.priority,QM.IsMultiple,QM.IsActive
from tbl_QuestionMaster QM inner join tbl_SubjectMaster SM on SM.SubjectId=QM.SubjectId inner join
tbl_TopicMaster TM on TM.topicId=QM.TopicId
WHERE QM.TopicId=@topicId
END

ELSE IF((@subId !=0) AND (@topicId=0))
BEGIN
select QM.QuestionId,SM.SubjectName,TM.topicName,QM.Question,QM.Answer,QM.Justification,QM.priority,QM.IsMultiple,QM.IsActive
from tbl_QuestionMaster QM inner join tbl_SubjectMaster SM on SM.SubjectId=QM.SubjectId inner join
tbl_TopicMaster TM on TM.topicId=QM.TopicId
WHERE QM.SubjectId=@subId
END

end

else if(@TransType=3)--for updation
begin
declare @countId int
declare @j int

update tbl_QuestionMaster 
set SubjectId=@subId,TopicId=@topicId,Question=@Question,Answer=@answer,Justification=@justification,priority=@priority,IsMultiple=@IsMutiple,IsActive=@IsActive
where QuestionId=@Qid

create table #temp(
id int primary key identity(1,1),
optionId varchar(5000),
)

create table #temp1(
id int primary key identity(1,1),
optionName varchar(5000)
)

create table #temp2(
id int primary key identity(1,1),
optionId varchar(5000),
optionName varchar(5000)
)

insert into #temp
select *from Mysplitfunction(@optionId,'|')

INSERT INTO #temp1
select *from Mysplitfunction(@option,'|')

insert into #temp2(optionId,optionName)
select t.optionId,t1.optionName from #temp t inner join #temp1 t1 on t.id=t1.id


set @countId=(select COUNT(id) from #temp2)
set @j=1

declare @opName varchar(5000)
declare @opId varchar(5000)

while(@countId>=@j)
begin
set @opId=(select t2.optionId from #temp2 t2 where t2.id=@j)
set @opName=(select t2.optionName from #temp2 t2 where t2.id=@j)

update tbl_QuestionOptionMaster
set Options=@opName
where OptionId=@opId

set @j=@j+1
end

end

else if(@TransType=4)
begin
	select QM.SubjectId,QM.TopicId,QM.Question,QOM.Options,QM.IsMultiple,QM.Answer,QM.Justification,QM.priority,QOM.OptionId,QM.IsActive
	from tbl_QuestionMaster QM inner join tbl_QuestionOptionMaster QOM on QM.QuestionId=QOM.QuestionId
	where QM.QuestionId=@QId
end
END



GO
/****** Object:  StoredProcedure [dbo].[sp_ModifyTaskTypeMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		yogi
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ModifyTaskTypeMaster]
	@type int,
	@TaskName varchar(50)=Null,
	@rbtnFollowUp varchar(50)=Null,
	@FollowUpType varchar(50)=Null
	
AS
BEGIN
--declare @TaskId int

	--set @TaskId=(select TM.TaskTypeId from tbl_TaskTypeMaster TM where TM.TaskName=@TaskName)
	update tbl_TaskTypeMaster set TaskName=@TaskName,FollowUp=@rbtnFollowUp,FollowUpType=@FollowUpType where TaskTypeId=@type




END


GO
/****** Object:  StoredProcedure [dbo].[sp_SetTopic]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ankur
-- Create date: 3/3/2011
-- Description:	to insert a new topic
-- =============================================
CREATE PROCEDURE [dbo].[sp_SetTopic]
@subId int,
@topicName varchar(50)
AS
BEGIN
insert into tbl_TopicMaster(topicName,subjectId) values(@topicName,@subId)
select TM.topicId from tbl_TopicMaster TM where TM.subjectId=@subId and TM.topicName=@topicName
END


GO
/****** Object:  StoredProcedure [dbo].[sp_TestInProgress]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<vijaya,Jansi>
-- Create date: <4-3-2011>
-- Description:	<Displying questions>
-- =============================================
CREATE PROCEDURE [dbo].[sp_TestInProgress]
 @TestId	INT = null,
 @TransType INT = null
AS
        
BEGIN
	
	SET NOCOUNT ON;
	
IF(@TransType = 1)
BEGIN
   SELECT tq.TestId,qm.Question, qm.QuestionId, qm.IsMultiple FROM tbl_TestQuestionMaster tq INNER JOIN tbl_QuestionMaster qm ON tq.QuestionId = qm.QuestionId
   AND tq.TestId = @TestId
END
END


GO
/****** Object:  UserDefinedFunction [dbo].[GetSubjectNameBySubjectId]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[GetSubjectNameBySubjectId] 
(
	@SubjectId int
)
RETURNS  varchar(50)
AS
BEGIN
    DECLARE @SubjectName Varchar(50)
    SELECT @SubjectName = SubjectName  from tbl_TestListMaster tl inner join tbl_SubjectMaster sm on tl.SubjectId = sm.SubjectId 
    
    RETURN @SubjectName
END

  

GO
/****** Object:  UserDefinedFunction [dbo].[GetTopicNameByTopicId]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[GetTopicNameByTopicId] 
(
	@TopicId varchar(50)
)
RETURNS  varchar(50)
AS
BEGIN
    DECLARE @topicName Varchar(50)
    SELECT @topicName = topicName from tbl_TopicMaster where topicId = @TopicId 
    
    RETURN @topicName
END


GO
/****** Object:  UserDefinedFunction [dbo].[Mysplitfunction]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ankur
-- Create date: 02/03/2011
-- Description:	Split funtion
-- =============================================
CREATE FUNCTION [dbo].[Mysplitfunction]
(
	@MyString varchar(5000), @Delimiter char(1)
)
RETURNS 
@temptable TABLE 
(
items varchar(5000)
)
AS
BEGIN
declare @idx int
declare @oneid varchar(5000)

select @idx = 1
if len(@MyString)<1 or @MyString is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@MyString)
if @idx!=0
set @oneid = left(@MyString,@idx - 1)
else
set @oneid = @MyString

if(len(@oneid)>0)
insert into @temptable(Items) values(@oneid)

set @MyString = right(@MyString,len(@MyString) - @idx)
if len(@MyString) = 0 break
end
return 
END


GO
/****** Object:  UserDefinedFunction [dbo].[UserSplitfunction]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		Ankur
-- Create date: 02/03/2011
-- Description:	Split funtion
-- =============================================
CREATE FUNCTION [dbo].[UserSplitfunction]
(
	@MyString varchar(5000), @Delimiter char(1)
)
RETURNS 
@temptable TABLE 
(
items uniqueidentifier
)
AS
BEGIN
declare @idx int
declare @oneid uniqueidentifier

select @idx = 1
if len(@MyString)<1 or @MyString is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@MyString)
if @idx!=0
set @oneid = cast(left(@MyString,@idx - 1)as uniqueidentifier)
else
set @oneid = cast(@MyString as uniqueidentifier)

if(len(@oneid)>0)
insert into @temptable(Items) values(@oneid)


set @MyString = right(@MyString,len(@MyString) - @idx)
if len(@MyString) = 0 break
end

return 
END


--SELECT * FROM UserSplitfunction('shankar|ramu','|')
GO
/****** Object:  Table [dbo].[aspnet_Applications]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Applications](
	[ApplicationName] [nvarchar](256) NOT NULL,
	[LoweredApplicationName] [nvarchar](256) NOT NULL,
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[Description] [nvarchar](256) NULL,
 CONSTRAINT [PK__aspnet_A__C93A4C9838996AB5] PRIMARY KEY NONCLUSTERED 
(
	[ApplicationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ__aspnet_A__17477DE43B75D760] UNIQUE NONCLUSTERED 
(
	[LoweredApplicationName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ__aspnet_A__309103313E52440B] UNIQUE NONCLUSTERED 
(
	[ApplicationName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_Membership]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Membership](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[Password] [nvarchar](128) NOT NULL,
	[PasswordFormat] [int] NOT NULL,
	[PasswordSalt] [nvarchar](128) NOT NULL,
	[MobilePIN] [nvarchar](16) NULL,
	[Email] [nvarchar](256) NULL,
	[LoweredEmail] [nvarchar](256) NULL,
	[PasswordQuestion] [nvarchar](256) NULL,
	[PasswordAnswer] [nvarchar](128) NULL,
	[IsApproved] [bit] NOT NULL,
	[IsLockedOut] [bit] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[LastLoginDate] [datetime] NOT NULL,
	[LastPasswordChangedDate] [datetime] NOT NULL,
	[LastLockoutDate] [datetime] NOT NULL,
	[FailedPasswordAttemptCount] [int] NOT NULL,
	[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
	[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
	[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
	[Comment] [ntext] NULL,
 CONSTRAINT [PK__aspnet_M__1788CC4D571DF1D5] PRIMARY KEY NONCLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_Paths]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Paths](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[PathId] [uniqueidentifier] NOT NULL,
	[Path] [nvarchar](256) NOT NULL,
	[LoweredPath] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK__aspnet_P__CD67DC5810566F31] PRIMARY KEY NONCLUSTERED 
(
	[PathId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_PersonalizationAllUsers]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers](
	[PathId] [uniqueidentifier] NOT NULL,
	[PageSettings] [image] NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK__aspnet_P__CD67DC5917F790F9] PRIMARY KEY CLUSTERED 
(
	[PathId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_PersonalizationPerUser]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_PersonalizationPerUser](
	[Id] [uniqueidentifier] NOT NULL,
	[PathId] [uniqueidentifier] NULL,
	[UserId] [uniqueidentifier] NULL,
	[PageSettings] [image] NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK__aspnet_P__3214EC061CBC4616] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_Profile]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Profile](
	[UserId] [uniqueidentifier] NOT NULL,
	[PropertyNames] [ntext] NOT NULL,
	[PropertyValuesString] [ntext] NOT NULL,
	[PropertyValuesBinary] [image] NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK__aspnet_P__1788CC4C6E01572D] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_Roles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Roles](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
	[RoleName] [nvarchar](256) NOT NULL,
	[LoweredRoleName] [nvarchar](256) NOT NULL,
	[Description] [nvarchar](256) NULL,
 CONSTRAINT [PK__aspnet_R__8AFACE1B797309D9] PRIMARY KEY NONCLUSTERED 
(
	[RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_SchemaVersions]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_SchemaVersions](
	[Feature] [nvarchar](128) NOT NULL,
	[CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
	[IsCurrentVersion] [bit] NOT NULL,
 CONSTRAINT [PK__aspnet_S__5A1E6BC14AB81AF0] PRIMARY KEY CLUSTERED 
(
	[Feature] ASC,
	[CompatibleSchemaVersion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_Users]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Users](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[UserName] [nvarchar](256) NOT NULL,
	[LoweredUserName] [nvarchar](256) NOT NULL,
	[MobileAlias] [nvarchar](16) NULL,
	[IsAnonymous] [bit] NOT NULL,
	[LastActivityDate] [datetime] NOT NULL,
 CONSTRAINT [PK__aspnet_U__1788CC4D4316F928] PRIMARY KEY NONCLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_UsersInRoles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_UsersInRoles](
	[UserId] [uniqueidentifier] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK__aspnet_U__AF2760AD7F2BE32F] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC,
	[RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_WebEvent_Events]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aspnet_WebEvent_Events](
	[EventId] [char](32) NOT NULL,
	[EventTimeUtc] [datetime] NOT NULL,
	[EventTime] [datetime] NOT NULL,
	[EventType] [nvarchar](256) NOT NULL,
	[EventSequence] [decimal](19, 0) NOT NULL,
	[EventOccurrence] [decimal](19, 0) NOT NULL,
	[EventCode] [int] NOT NULL,
	[EventDetailCode] [int] NOT NULL,
	[Message] [nvarchar](1024) NULL,
	[ApplicationPath] [nvarchar](256) NULL,
	[ApplicationVirtualPath] [nvarchar](256) NULL,
	[MachineName] [nvarchar](256) NOT NULL,
	[RequestUrl] [nvarchar](1024) NULL,
	[ExceptionType] [nvarchar](256) NULL,
	[Details] [ntext] NULL,
 CONSTRAINT [PK__aspnet_W__7944C81030C33EC3] PRIMARY KEY CLUSTERED 
(
	[EventId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_AssignmentMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_AssignmentMaster](
	[AssignmentId] [int] IDENTITY(1,1) NOT NULL,
	[AssignmentName] [varchar](50) NULL,
	[SubjectId] [int] NULL,
	[TopicId] [int] NULL,
	[Question] [varchar](3000) NULL,
	[Attachment] [varchar](50) NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[States] [varchar](50) NULL,
	[Notify] [int] NULL,
 CONSTRAINT [PK_tbl_AssignmentMaster] PRIMARY KEY CLUSTERED 
(
	[AssignmentId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_AssignmentTrans]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_AssignmentTrans](
	[AssignmentId] [int] NULL,
	[UserId] [uniqueidentifier] NULL,
	[States] [varchar](50) NULL,
	[AssignmentPath] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_AssignmentTransHistory]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_AssignmentTransHistory](
	[AssignmentId] [int] NULL,
	[UserId] [uniqueidentifier] NULL,
	[Remarks] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_GroupsMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_GroupsMaster](
	[int_GroupID] [int] IDENTITY(1,1) NOT NULL,
	[vc_GroupName] [varchar](50) NOT NULL,
	[vc_Description] [varchar](100) NULL,
	[int_IsActive] [int] NOT NULL,
 CONSTRAINT [PK_tbl_GroupsMaster] PRIMARY KEY CLUSTERED 
(
	[int_GroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_QuestionMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_QuestionMaster](
	[QuestionId] [int] IDENTITY(1,1) NOT NULL,
	[SubjectId] [int] NULL,
	[TopicId] [int] NULL,
	[Question] [varchar](3000) NULL,
	[Answer] [varchar](50) NULL,
	[Justification] [varchar](500) NULL,
	[priority] [varchar](50) NULL,
	[IsMultiple] [int] NULL,
	[IsActive] [int] NULL,
 CONSTRAINT [PK__tbl_Ques__0DC06FAC0425A276] PRIMARY KEY CLUSTERED 
(
	[QuestionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_QuestionOptionMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_QuestionOptionMaster](
	[OptionId] [int] IDENTITY(1,1) NOT NULL,
	[Options] [varchar](1000) NULL,
	[QuestionId] [int] NULL,
 CONSTRAINT [PK_tbl_QuestionOptionMaster] PRIMARY KEY CLUSTERED 
(
	[OptionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_SubjectMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_SubjectMaster](
	[SubjectId] [int] IDENTITY(1,1) NOT NULL,
	[SubjectName] [varchar](50) NULL,
	[IsActive] [int] NULL,
 CONSTRAINT [PK_tbl_SubjectMaster] PRIMARY KEY CLUSTERED 
(
	[SubjectId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_TaskMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TaskMaster](
	[TaskID] [int] IDENTITY(1,1) NOT NULL,
	[TaskName] [varchar](100) NULL,
	[TaskDesc] [varchar](300) NULL,
	[TaskTypeID] [int] NULL,
	[TaskOwnerID] [int] NULL,
	[SubjectID] [int] NULL,
	[TopicID] [varchar](300) NULL,
	[LibraryID] [int] NULL,
	[IsVerificationRequired] [int] NULL,
	[TestID] [int] NULL,
	[StartDate] [smalldatetime] NULL,
	[EndDate] [smalldatetime] NULL,
	[status] [int] NULL,
 CONSTRAINT [PK_tbl_TaskMaster] PRIMARY KEY CLUSTERED 
(
	[TaskID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_TaskTransactions]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TaskTransactions](
	[TaskID] [int] NULL,
	[GroupID] [int] NULL,
	[UserID] [uniqueidentifier] NULL,
	[Status] [varchar](50) NULL,
	[Remarks] [varchar](300) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_TaskTypeMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TaskTypeMaster](
	[TaskTypeId] [int] IDENTITY(1,1) NOT NULL,
	[TaskName] [varchar](50) NOT NULL,
	[FollowUp] [varchar](50) NOT NULL,
	[FollowUpType] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_TaskTypeMaster] PRIMARY KEY CLUSTERED 
(
	[TaskTypeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_TestAttemptMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TestAttemptMaster](
	[TestAttemptId] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [uniqueidentifier] NULL,
	[TestId] [int] NULL,
	[AttemptNo] [int] NULL,
	[Result] [varchar](50) NULL,
	[Score] [int] NULL,
	[AttemptDate] [datetime] NULL,
	[TimeTaken] [int] NULL,
 CONSTRAINT [PK_tbl_TestAttemptMaster] PRIMARY KEY CLUSTERED 
(
	[TestAttemptId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_TestAttemptQuestion]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TestAttemptQuestion](
	[TestAttemptId] [int] NULL,
	[QuestionId] [int] NULL,
	[OptionId] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_TestListMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TestListMaster](
	[TestId] [int] IDENTITY(1,1) NOT NULL,
	[TestName] [varchar](50) NULL,
	[SubjectId] [int] NULL,
	[IsQuestionManualSelection] [int] NULL,
	[NoOfQuestion] [int] NULL,
	[NoOfHighQuestion] [int] NULL,
	[NoOfMediumQuestion] [int] NULL,
	[NoOfLowQuestion] [int] NULL,
	[CutOffMarks] [int] NULL,
	[TestCreatedDate] [datetime] NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL,
	[Duration] [int] NULL,
	[sendCumulativeReport] [int] NULL,
	[NoOfAttempts] [int] NULL,
	[CountofAttempts] [int] NULL,
	[RefLibrary] [varchar](50) NULL,
	[States] [varchar](50) NULL,
	[NotifyTestTaker] [int] NULL,
 CONSTRAINT [PK_tbl_TestListMaster] PRIMARY KEY CLUSTERED 
(
	[TestId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_TestQuestionMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_TestQuestionMaster](
	[TestId] [int] NULL,
	[QuestionId] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tbl_TestTopicMapping]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_TestTopicMapping](
	[TestId] [int] NULL,
	[TopicId] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tbl_TestUserMapping]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TestUserMapping](
	[TestId] [int] NULL,
	[UserId] [uniqueidentifier] NULL,
	[States] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_TopicMaster]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_TopicMaster](
	[topicId] [int] IDENTITY(1,1) NOT NULL,
	[topicName] [varchar](50) NULL,
	[subjectId] [int] NULL,
 CONSTRAINT [PK_tbl_TopicMaster] PRIMARY KEY CLUSTERED 
(
	[topicId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tbl_UserGroups]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_UserGroups](
	[UserID] [uniqueidentifier] NOT NULL,
	[GroupID] [int] NOT NULL
) ON [PRIMARY]

GO
/****** Object:  UserDefinedFunction [dbo].[ufn_TopicNames]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create function [dbo].[ufn_TopicNames](@SubjectID int)
RETURNS TABLE
as
return select topicName from tbl_TopicMaster where subjectId=@SubjectId

GO
/****** Object:  View [dbo].[vw_aspnet_Applications]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_Applications]
  AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
  FROM [dbo].[aspnet_Applications]
  
GO
/****** Object:  View [dbo].[vw_aspnet_MembershipUsers]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
  AS SELECT [dbo].[aspnet_Membership].[UserId],
            [dbo].[aspnet_Membership].[PasswordFormat],
            [dbo].[aspnet_Membership].[MobilePIN],
            [dbo].[aspnet_Membership].[Email],
            [dbo].[aspnet_Membership].[LoweredEmail],
            [dbo].[aspnet_Membership].[PasswordQuestion],
            [dbo].[aspnet_Membership].[PasswordAnswer],
            [dbo].[aspnet_Membership].[IsApproved],
            [dbo].[aspnet_Membership].[IsLockedOut],
            [dbo].[aspnet_Membership].[CreateDate],
            [dbo].[aspnet_Membership].[LastLoginDate],
            [dbo].[aspnet_Membership].[LastPasswordChangedDate],
            [dbo].[aspnet_Membership].[LastLockoutDate],
            [dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
            [dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
            [dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
            [dbo].[aspnet_Membership].[Comment],
            [dbo].[aspnet_Users].[ApplicationId],
            [dbo].[aspnet_Users].[UserName],
            [dbo].[aspnet_Users].[MobileAlias],
            [dbo].[aspnet_Users].[IsAnonymous],
            [dbo].[aspnet_Users].[LastActivityDate]
  FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
      ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]
  
GO
/****** Object:  View [dbo].[vw_aspnet_Profiles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_Profiles]
  AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
      [DataSize]=  DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
                 + DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
  FROM [dbo].[aspnet_Profile]
  
GO
/****** Object:  View [dbo].[vw_aspnet_Roles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_Roles]
  AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
  FROM [dbo].[aspnet_Roles]
  
GO
/****** Object:  View [dbo].[vw_aspnet_Users]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_Users]
  AS SELECT [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[LoweredUserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate]
  FROM [dbo].[aspnet_Users]
  
GO
/****** Object:  View [dbo].[vw_aspnet_UsersInRoles]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
  AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
  FROM [dbo].[aspnet_UsersInRoles]
  
GO
/****** Object:  View [dbo].[vw_aspnet_WebPartState_Paths]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_WebPartState_Paths]
  AS SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]
  FROM [dbo].[aspnet_Paths]
  
GO
/****** Object:  View [dbo].[vw_aspnet_WebPartState_Shared]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_WebPartState_Shared]
  AS SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]
  FROM [dbo].[aspnet_PersonalizationAllUsers]
  
GO
/****** Object:  View [dbo].[vw_aspnet_WebPartState_User]    Script Date: 4/5/2011 9:48:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

  CREATE VIEW [dbo].[vw_aspnet_WebPartState_User]
  AS SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]
  FROM [dbo].[aspnet_PersonalizationPerUser]
  
GO
ALTER TABLE [dbo].[aspnet_Applications] ADD  CONSTRAINT [DF__aspnet_Ap__Appli__403A8C7D]  DEFAULT (newid()) FOR [ApplicationId]
GO
ALTER TABLE [dbo].[aspnet_Membership] ADD  CONSTRAINT [DF__aspnet_Me__Passw__5AEE82B9]  DEFAULT ((0)) FOR [PasswordFormat]
GO
ALTER TABLE [dbo].[aspnet_Paths] ADD  CONSTRAINT [DF__aspnet_Pa__PathI__1332DBDC]  DEFAULT (newid()) FOR [PathId]
GO
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] ADD  CONSTRAINT [DF__aspnet_Perso__Id__1EA48E88]  DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[aspnet_Roles] ADD  CONSTRAINT [DF__aspnet_Ro__RoleI__7C4F7684]  DEFAULT (newid()) FOR [RoleId]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD  CONSTRAINT [DF__aspnet_Us__UserI__45F365D3]  DEFAULT (newid()) FOR [UserId]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD  CONSTRAINT [DF__aspnet_Us__Mobil__46E78A0C]  DEFAULT (NULL) FOR [MobileAlias]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD  CONSTRAINT [DF__aspnet_Us__IsAno__47DBAE45]  DEFAULT ((0)) FOR [IsAnonymous]
GO
ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Me__Appli__59063A47] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_Membership] CHECK CONSTRAINT [FK__aspnet_Me__Appli__59063A47]
GO
ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Me__UserI__59FA5E80] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[aspnet_Membership] CHECK CONSTRAINT [FK__aspnet_Me__UserI__59FA5E80]
GO
ALTER TABLE [dbo].[aspnet_Paths]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Pa__Appli__123EB7A3] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_Paths] CHECK CONSTRAINT [FK__aspnet_Pa__Appli__123EB7A3]
GO
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Pe__PathI__19DFD96B] FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
GO
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers] CHECK CONSTRAINT [FK__aspnet_Pe__PathI__19DFD96B]
GO
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Pe__PathI__1F98B2C1] FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
GO
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] CHECK CONSTRAINT [FK__aspnet_Pe__PathI__1F98B2C1]
GO
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Pe__UserI__208CD6FA] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] CHECK CONSTRAINT [FK__aspnet_Pe__UserI__208CD6FA]
GO
ALTER TABLE [dbo].[aspnet_Profile]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Pr__UserI__6FE99F9F] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[aspnet_Profile] CHECK CONSTRAINT [FK__aspnet_Pr__UserI__6FE99F9F]
GO
ALTER TABLE [dbo].[aspnet_Roles]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Ro__Appli__7B5B524B] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_Roles] CHECK CONSTRAINT [FK__aspnet_Ro__Appli__7B5B524B]
GO
ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Us__Appli__44FF419A] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_Users] CHECK CONSTRAINT [FK__aspnet_Us__Appli__44FF419A]
GO
ALTER TABLE [dbo].[aspnet_UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Us__RoleI__02084FDA] FOREIGN KEY([RoleId])
REFERENCES [dbo].[aspnet_Roles] ([RoleId])
GO
ALTER TABLE [dbo].[aspnet_UsersInRoles] CHECK CONSTRAINT [FK__aspnet_Us__RoleI__02084FDA]
GO
ALTER TABLE [dbo].[aspnet_UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK__aspnet_Us__UserI__01142BA1] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[aspnet_UsersInRoles] CHECK CONSTRAINT [FK__aspnet_Us__UserI__01142BA1]
GO
ALTER TABLE [dbo].[tbl_AssignmentMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_AssignmentMaster_tbl_SubjectMaster] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_AssignmentMaster] CHECK CONSTRAINT [FK_tbl_AssignmentMaster_tbl_SubjectMaster]
GO
ALTER TABLE [dbo].[tbl_AssignmentMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_AssignmentMaster_tbl_TopicMaster] FOREIGN KEY([TopicId])
REFERENCES [dbo].[tbl_TopicMaster] ([topicId])
GO
ALTER TABLE [dbo].[tbl_AssignmentMaster] CHECK CONSTRAINT [FK_tbl_AssignmentMaster_tbl_TopicMaster]
GO
ALTER TABLE [dbo].[tbl_AssignmentTrans]  WITH CHECK ADD  CONSTRAINT [FK_tbl_AssignmentTrans_aspnet_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[tbl_AssignmentTrans] CHECK CONSTRAINT [FK_tbl_AssignmentTrans_aspnet_Users]
GO
ALTER TABLE [dbo].[tbl_AssignmentTrans]  WITH CHECK ADD  CONSTRAINT [FK_tbl_AssignmentTrans_tbl_AssignmentMaster] FOREIGN KEY([AssignmentId])
REFERENCES [dbo].[tbl_AssignmentMaster] ([AssignmentId])
GO
ALTER TABLE [dbo].[tbl_AssignmentTrans] CHECK CONSTRAINT [FK_tbl_AssignmentTrans_tbl_AssignmentMaster]
GO
ALTER TABLE [dbo].[tbl_AssignmentTransHistory]  WITH CHECK ADD  CONSTRAINT [FK_tbl_AssignmentTransHistory_aspnet_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[tbl_AssignmentTransHistory] CHECK CONSTRAINT [FK_tbl_AssignmentTransHistory_aspnet_Users]
GO
ALTER TABLE [dbo].[tbl_AssignmentTransHistory]  WITH CHECK ADD  CONSTRAINT [FK_tbl_AssignmentTransHistory_tbl_AssignmentMaster] FOREIGN KEY([AssignmentId])
REFERENCES [dbo].[tbl_AssignmentMaster] ([AssignmentId])
GO
ALTER TABLE [dbo].[tbl_AssignmentTransHistory] CHECK CONSTRAINT [FK_tbl_AssignmentTransHistory_tbl_AssignmentMaster]
GO
ALTER TABLE [dbo].[tbl_QuestionMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_QuestionMaster_tbl_SubjectMaster] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_QuestionMaster] CHECK CONSTRAINT [FK_tbl_QuestionMaster_tbl_SubjectMaster]
GO
ALTER TABLE [dbo].[tbl_QuestionMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_QuestionMaster_tbl_TopicMaster] FOREIGN KEY([TopicId])
REFERENCES [dbo].[tbl_TopicMaster] ([topicId])
GO
ALTER TABLE [dbo].[tbl_QuestionMaster] CHECK CONSTRAINT [FK_tbl_QuestionMaster_tbl_TopicMaster]
GO
ALTER TABLE [dbo].[tbl_QuestionOptionMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_QuestionOptionMaster_tbl_QuestionMaster] FOREIGN KEY([QuestionId])
REFERENCES [dbo].[tbl_QuestionMaster] ([QuestionId])
GO
ALTER TABLE [dbo].[tbl_QuestionOptionMaster] CHECK CONSTRAINT [FK_tbl_QuestionOptionMaster_tbl_QuestionMaster]
GO
ALTER TABLE [dbo].[tbl_TaskMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TaskMaster_tbl_TaskTypeMaster] FOREIGN KEY([TaskTypeID])
REFERENCES [dbo].[tbl_TaskTypeMaster] ([TaskTypeId])
GO
ALTER TABLE [dbo].[tbl_TaskMaster] CHECK CONSTRAINT [FK_tbl_TaskMaster_tbl_TaskTypeMaster]
GO
ALTER TABLE [dbo].[tbl_TaskMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TaskMaster_tbl_TestListMaster] FOREIGN KEY([TestID])
REFERENCES [dbo].[tbl_TestListMaster] ([TestId])
GO
ALTER TABLE [dbo].[tbl_TaskMaster] CHECK CONSTRAINT [FK_tbl_TaskMaster_tbl_TestListMaster]
GO
ALTER TABLE [dbo].[tbl_TaskTransactions]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TaskTransactions_aspnet_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[tbl_TaskTransactions] CHECK CONSTRAINT [FK_tbl_TaskTransactions_aspnet_Users]
GO
ALTER TABLE [dbo].[tbl_TaskTransactions]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TaskTransactions_tbl_GroupsMaster] FOREIGN KEY([GroupID])
REFERENCES [dbo].[tbl_GroupsMaster] ([int_GroupID])
GO
ALTER TABLE [dbo].[tbl_TaskTransactions] CHECK CONSTRAINT [FK_tbl_TaskTransactions_tbl_GroupsMaster]
GO
ALTER TABLE [dbo].[tbl_TaskTransactions]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TaskTransactions_tbl_TaskMaster] FOREIGN KEY([TaskID])
REFERENCES [dbo].[tbl_TaskMaster] ([TaskID])
GO
ALTER TABLE [dbo].[tbl_TaskTransactions] CHECK CONSTRAINT [FK_tbl_TaskTransactions_tbl_TaskMaster]
GO
ALTER TABLE [dbo].[tbl_TestAttemptQuestion]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TestAttemptQuestion_tbl_QuestionMaster] FOREIGN KEY([QuestionId])
REFERENCES [dbo].[tbl_QuestionMaster] ([QuestionId])
GO
ALTER TABLE [dbo].[tbl_TestAttemptQuestion] CHECK CONSTRAINT [FK_tbl_TestAttemptQuestion_tbl_QuestionMaster]
GO
ALTER TABLE [dbo].[tbl_TestAttemptQuestion]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TestAttemptQuestion_tbl_TestAttemptMaster] FOREIGN KEY([TestAttemptId])
REFERENCES [dbo].[tbl_TestAttemptMaster] ([TestAttemptId])
GO
ALTER TABLE [dbo].[tbl_TestAttemptQuestion] CHECK CONSTRAINT [FK_tbl_TestAttemptQuestion_tbl_TestAttemptMaster]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__182C9B23] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__182C9B23]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__1920BF5C] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__1920BF5C]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__1A14E395] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__1A14E395]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__1B0907CE] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__1B0907CE]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__1BFD2C07] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__1BFD2C07]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__1CF15040] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__1CF15040]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__1DE57479] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__1DE57479]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__1ED998B2] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__1ED998B2]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__1FCDBCEB] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__1FCDBCEB]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__20C1E124] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__20C1E124]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__21B6055D] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__21B6055D]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__22AA2996] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__22AA2996]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__239E4DCF] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__239E4DCF]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__24927208] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__24927208]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__25869641] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__25869641]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__267ABA7A] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__267ABA7A]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__276EDEB3] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__276EDEB3]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__286302EC] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__286302EC]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__29572725] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__29572725]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__2A4B4B5E] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__2A4B4B5E]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__2B3F6F97] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__2B3F6F97]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__2C3393D0] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__2C3393D0]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__2D27B809] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__2D27B809]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__2E1BDC42] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__2E1BDC42]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__4E53A1AA] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__4E53A1AA]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__4F47C5E3] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__4F47C5E3]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__503BEA1C] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__503BEA1C]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__51300E55] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__51300E55]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__5224328E] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__5224328E]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__531856C7] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__531856C7]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__540C7B00] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__540C7B00]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__55009F39] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__55009F39]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__55F4C372] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__55F4C372]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__56E8E7AB] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__56E8E7AB]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__57DD0BE4] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__57DD0BE4]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__58D1301D] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__58D1301D]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__59C55456] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__59C55456]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__5AB9788F] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__5AB9788F]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__5BAD9CC8] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__5BAD9CC8]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__5CA1C101] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__5CA1C101]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__5D95E53A] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__5D95E53A]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__5E8A0973] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__5E8A0973]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__5F7E2DAC] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__5F7E2DAC]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__607251E5] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__607251E5]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__6166761E] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__6166761E]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__625A9A57] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__625A9A57]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__634EBE90] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__634EBE90]
GO
ALTER TABLE [dbo].[tbl_TestListMaster]  WITH CHECK ADD  CONSTRAINT [FK__tbl_TestL__Subje__6442E2C9] FOREIGN KEY([SubjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TestListMaster] CHECK CONSTRAINT [FK__tbl_TestL__Subje__6442E2C9]
GO
ALTER TABLE [dbo].[tbl_TestQuestionMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TestQuestionMaster_tbl_QuestionMaster] FOREIGN KEY([QuestionId])
REFERENCES [dbo].[tbl_QuestionMaster] ([QuestionId])
GO
ALTER TABLE [dbo].[tbl_TestQuestionMaster] CHECK CONSTRAINT [FK_tbl_TestQuestionMaster_tbl_QuestionMaster]
GO
ALTER TABLE [dbo].[tbl_TestQuestionMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TestQuestionMaster_tbl_TestListMaster] FOREIGN KEY([TestId])
REFERENCES [dbo].[tbl_TestListMaster] ([TestId])
GO
ALTER TABLE [dbo].[tbl_TestQuestionMaster] CHECK CONSTRAINT [FK_tbl_TestQuestionMaster_tbl_TestListMaster]
GO
ALTER TABLE [dbo].[tbl_TestTopicMapping]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TestTopicMapping_tbl_TestListMaster] FOREIGN KEY([TestId])
REFERENCES [dbo].[tbl_TestListMaster] ([TestId])
GO
ALTER TABLE [dbo].[tbl_TestTopicMapping] CHECK CONSTRAINT [FK_tbl_TestTopicMapping_tbl_TestListMaster]
GO
ALTER TABLE [dbo].[tbl_TestTopicMapping]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TestTopicMapping_tbl_TopicMaster] FOREIGN KEY([TopicId])
REFERENCES [dbo].[tbl_TopicMaster] ([topicId])
GO
ALTER TABLE [dbo].[tbl_TestTopicMapping] CHECK CONSTRAINT [FK_tbl_TestTopicMapping_tbl_TopicMaster]
GO
ALTER TABLE [dbo].[tbl_TestUserMapping]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TestUserMapping_aspnet_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[tbl_TestUserMapping] CHECK CONSTRAINT [FK_tbl_TestUserMapping_aspnet_Users]
GO
ALTER TABLE [dbo].[tbl_TestUserMapping]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TestUserMapping_tbl_TestListMaster] FOREIGN KEY([TestId])
REFERENCES [dbo].[tbl_TestListMaster] ([TestId])
GO
ALTER TABLE [dbo].[tbl_TestUserMapping] CHECK CONSTRAINT [FK_tbl_TestUserMapping_tbl_TestListMaster]
GO
ALTER TABLE [dbo].[tbl_TopicMaster]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TopicMaster_tbl_SubjectMaster] FOREIGN KEY([subjectId])
REFERENCES [dbo].[tbl_SubjectMaster] ([SubjectId])
GO
ALTER TABLE [dbo].[tbl_TopicMaster] CHECK CONSTRAINT [FK_tbl_TopicMaster_tbl_SubjectMaster]
GO
ALTER TABLE [dbo].[tbl_UserGroups]  WITH CHECK ADD  CONSTRAINT [FK_tbl_UserGroups_aspnet_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[tbl_UserGroups] CHECK CONSTRAINT [FK_tbl_UserGroups_aspnet_Users]
GO
ALTER TABLE [dbo].[tbl_UserGroups]  WITH CHECK ADD  CONSTRAINT [FK_tbl_UserGroups_tbl_GroupsMaster] FOREIGN KEY([GroupID])
REFERENCES [dbo].[tbl_GroupsMaster] ([int_GroupID])
GO
ALTER TABLE [dbo].[tbl_UserGroups] CHECK CONSTRAINT [FK_tbl_UserGroups_tbl_GroupsMaster]
GO
